Make Undo from macro in all cases!

Jiurasic

Board Regular
Joined
Sep 22, 2007
Messages
86
Hello to all!
Here is the task.
For example I want to make my own sheet protection from macros.
I Write such code in Sheet 1:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    MsgBox "You can't do it!"
    Application.Undo
Application.EnableEvents = True
End Sub

As you see – if user try to change text in any cell, or paste values the program shows a mistake. BUT!
It doesn’t work in the case when user select one or more cells and drag then into others.
What I mean drag? I mean that you put mouse to be border on the cell, click left button down and then drug the context of the cells into other position.

This code execute twice – I don’t understand why?
I think you will see it if you try…



Have any ideas to make this macros worked right in all cases?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top