In the following code I' am getting error while UNDO, marked with ##########
I' think this is because there is nothing pending to do undo in the excel file, please help me with it.
For Each MergedColumn1 In Target.Columns
colcount = colcount + 1
If MergedColumn1.Column = 2 Then
Found = False
For Each MyCells In MergedColumn1.Cells
If Len(MyCells.Value) > 0 Then
Call GetNameRange(MyCells, MyCells.Value, ThisWorkbook.ActiveSheet)
NewVal = MyCells.Value
If Found = False Then
MsgBox Target.Cells.Address(False, False) & "'s @ MSG #1 Value Is Not From Given List, Hence Last Action Didn't Proceed" & vbCrLf & "(Please Use Drop Down And Select The Option Available In The List)"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
NewVal = ""
Else
'For multiple selection
If colcount > 1 Then
'do nothing
Else
Application.EnableEvents = False
NewVal = MyCells.Value
Application.Undo '##################################
OldVal = MyCells.Value
'LookUp OldVal, NewVal
MyCells.Value = NewVal
lUsed = InStr(1, OldVal, NewVal)
If lUsed > 0 Then
If Right(OldVal, Len(NewVal)) = NewVal Then
If Len(OldVal) - Len(NewVal) <= 0 Then
'do nothing
Else
MyCells.Value = Left(OldVal, Len(OldVal) - Len(NewVal) - 2)
End If
Else
MyCells.Value = Replace(OldVal, NewVal & "," & strSep, "")
End If
Else
If Len(OldVal) <= 0 Then
MyCells.Value = NewVal
Else
MyCells.Value = OldVal & "," & strSep & NewVal
End If
End If
Application.EnableEvents = True
End If
End If
Else
' If MergedColumn1.Column = 2 Then
' MergedColumn1.Validation.Delete
' MergedColumn1.Validation.Add xlValidateList, , , "=INDIRECT($B$5)"
' End If
End If
Next
End If
Bookmarks