How to stop users from pasting over list box cells?

  • Thread starter Thread starter sp97dktheta
  • Start date Start date
S

sp97dktheta

Hello!

I have created an excel worksheet that uses validation to force the
users to select a value from a list box. This was great until users
figured out that they can over-right the cells by copying and pasting
into the cells.

How can I stop them from being able to paste over these cells?

Any help would be greatly appreciated! Thanks!!!!

Mark G.
 
This won't stop them if they're industrious, but if they do things in a "normal"
manner, it could at least dampen their spirits.

Right click on the excel icon to the left of File on the menubar.
Select View code and paste this in:

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.CutCopyMode = False
End Sub

The macro says to empty that clipboard each time they change selection. But

If they disable events or just say no to macros, then this will fail.

If they copy to another application (or even another worksheet), then select the
receiving cell, then copy again, then swap back to that worksheet/cell and just
paste, they'll win.

=======
Another option would be to move the data|validation rules to VBA (and event
macro) that would check each time the worksheet is changed.
 
Back
Top