There doesn't seem to be any way to turn this feature off. However, there are three methods that I know of that can stop it happening. These are:
- Close Excel - this seems to me to be a bit of a nuclear option. You have to save all your work, close the application and then reopen all the spreadsheets again. Boring.
- Use the 'Text to Columns' feature but with different options. This sets the TTC procedure to a different delimiter and so you can now paste without data wandering off into subsequent columns. Annoying.
- Use a macro to reset the TTC options. It's described below. Better.
The macro is very simple and just repeats the steps that you'd take in option 2 above.
The Code
1 Dim v As Variant 2 3 v = ActiveCell 4 ActiveCell.Value = "Rem" 5 ActiveCell.TextToColumns Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, DataType:=xlDelimited 6 ActiveCell = v
Line 1 declares a variable to hold whatever is in the current active cell. Line 3 then populates this variable with the ActiveCell object. Line 4 puts a temporary value in this cell. Then in line 5 we run a TTC with no delimiters. This resets the paste options. Line 6 resets the active cell.
There are a few problems with writing the macro like this.
- For a start, you have to select a cell.
- This cell can have no protection enabled.
- A worksheet/book has to be open.
- The active cell can't be part of a pivot table.
Survey Science Add-in
A better way would be to include this procedure as part of an add-in that could be run via a button in a custom ribbon and use a worksheet in the add-in. This way it can be run with a single click and you know what cell it was over-writing.
And this is how I've implemented it in my add-in.
As usual comments are welcome.
