Allow Ctrl+V as Paste Special (VBA)

[Jasesair]

I have a range of cells that I'd like Ctrl+V to function differently for the end user. When users copy-paste from elsewhere into cells C12:C46 - worksheet name "Sheet A", I'd like that to paste in as text without affecting the formatting. The end user may paste a range of names or just one name.

Any help would be much appreciated.

 

[Michael M]

You could use CTRL+ALT+V, then select values from the dropdown !

 

[Jasesair]

The end user could be one of 1000 people. It's very hard to control the end user so I need a spreadsheet that's foolproof...so the end user needs to simply hit Ctrl-V to enter their list of names. I need a way to avoid any disruption to formatting.

 

[Michael M]

You'll probably need to use VBA....but sadly, there is no such thing as "foolproof"

 

[Jasesair]

Yes, I'm aware I'll need VBA. What I'm attempted hasn't worked so far, so seeking member support.

 

[Michael M]

1. What is the cell range to be copied ?
BUT, if you select any one of the required cells simply to make a data change, using something like a worksheet_selection change event will copy the range across each time ?
So how about this way....put this code in Sheet "A" sheet module.
So anything that gets put in C12:C46 will be treated as values only

Private Sub worksheet_Change(ByVal target As Range)
If Intersect(target, Range("C12")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("C12:C46")
.Value = .Value
End With
Application.EnableEvents = True
End Sub