Dim bUNDONE As Boolean 'used to make sure we do not have multiple undo's for 1 action
Private Sub Worksheet_Activate()
bUNDONE = False 'set bundone to false. It needs to always start false.
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sUndo As String
'test to see if the user deleted or added a row.
If Target.Rows(1).Cells.Count = Columns.Count Then
'bundone is used to make sure we only ask and undo one time. If we don't have bundone
'when the program undoes the remove row, we will fire the worksheet change event again and
'the user will get confused because there will be multiple times it asks if we want to undo.
'Bundone always starts false. Make it true for the first call then change it back.
If bUNDONE = False Then
bUNDONE = True
Else
bUNDONE = False
End If
If bUNDONE = True Then 'the user deleted a row and we have not yet asked if they want to do this
s = MsgBox("ALERT!!!! You have deleted or added a row." & vbLf & _
"Removing or adding rows may affect the functionality of the program." & vbLf & _
"Are you sure you want to make this change? (click Yes to make change, click No to undo.)", vbYesNo, Title:="ALERT:")
End If
If s = vbNo And bUNDONE = True Then 'the user changed their mind and does not want to delete
Application.Undo
Else
If s = vbYes Then bUNDONE = False 'if the user deleted and did not change His/Her mind, this will correct bundone.
End If
End If
End Sub