0

I have a workbook with three excel sheets in it. I want a user to be able to copy data from the third sheet which is result of calculations in the second sheet. How ever, the code I am using 

'The code for the Macro
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim col As String
Dim msg As String
col = Split(Target.Address(1, 0), "AOS")(0)
'Permet d'éviter les modifications des modifications manuelles des cellules calculées automatiquement
If Intersect(Target, Sheets("AOS").Range("A3:AP100")) Is Nothing Then Exit Sub

'We're going to make a change, so turn this off
'so that macro doesn't get called infinitely
Sheets("AOS").Application.EnableEvents = False

Sheets("AOS").Application.Undo
MsgBox "Can't touch this!", vbCritical + vbOKOnly, "Error !!!!!"
Sheets("AOS").Application.EnableEvents = True

It is also undo changes made in second sheet. Seems like, Sheets("AOS").Application.Undo is not working as it should be.

Any Suggestions..?

Ameya
  • 69
  • 2
  • 10

2 Answers2

0

You are using the SelectionChange event, you should be using the Change event. SelectionChange occurs every time the selection is changed, so when you use Undo it is reversing the last reversible action, which might have happened in a different sheet.

Andy G
  • 19,232
  • 5
  • 47
  • 69
0

From MSDN:

This method undoes only the last action taken by the user before running the macro, and it must be the first line in the macro. It cannot be used to undo Visual Basic commands.

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56