Undo not working after Worksheet_Change Sub Routine in Excel
-
In excel, I have listboxes (validation) in column A, and columb B. The ones in A work. I wrote code to check to see when there's a change in any item in A, and if the user hit 'No' on the MsgBox, then the change would be 'Undone' (ie. managed to use Undo in VB code). My problem is, I'm using virtually the same code for column B. However, columb B's list items are a validated list depending on what was selected in A. Whenever a change is made in B, and the user hits no, Undo is NOT executed. Any ideas as to why this is happening? Here's a sample of the code that is working for A: (Note that the code I have for B is the exact same with different Range references) '''Code start'''' Public undoCheck As Boolean Private Sub Worksheet_Change(ByVal Target As Excel.Range) If undoCheck Then 'do nothing undoCheck = False Else Dim i As Integer Dim rowNum As Integer i = 10 While i < 85 If (Target.Address = "$A$" & i) And (Cells(i, 2).Value <> "" Or Cells(i, 3).Value <> "" Or Cells(i, 4).Value <> "" Or Cells(i, 5).Value <> "" Or Cells(i, 6).Value <> "" Or Cells(i, 7).Value <> "" Or Cells(i, 8).Value <> "") Then If MsgBox("You are changing Element Data. Related Element Data will be removed but calculation data will remain. Do you wish to continue?", vbYesNo) = VbMsgBoxResult.vbYes Then 'clear values Range("B" & i).Value = "" Range("C" & i).Value = "" Range("D" & i).Value = "" Range("E" & i).Value = "" Range("F" & i).Value = "" Range("G" & i).Value = "" Range("H" & i).Value = "" Range("I" & i).Value = "" 'highlight colours Range("C" & i).Interior.Color = vbYellow Range("D" & i).Interior.Color = vbYellow Range("E" & i).Interior.Color = vbYellow Range("F" & i).Interior.Color = vbYellow Range("G" & i).Interior.Color = vbYellow Range("H" & i).Interior.Color = vbYellow Range("I" & i).Interior.Color = vbYellow undoCheck = False Exit Sub Else undoCheck = True If Application.CommandBars.FindControl(ID:=128).Enabled = True Then Excel.Application.Undo