cancel a combobox change

M

Mike Archer

Hello. I want to be able to cancel a combobox change. Cancel is not
parameter in the change event, and there is not a beforechange event. I have
pasted my attempt below. No matter what item is selected in the event for
the combobox, it always changes back to whatever the user selected at the
conclusion of the event. Is there a work around that would effectivly be the
same as canceling the change?

Private Sub ComboBox1_Change()
Dim RUSure As Integer, GoBack As Integer
GoBack = ComboBox1.ListIndex 'set the goback value in case user wants to
cancel.
If SavedList = False Then
RUSure = MsgBox("Do you want discard changes to the list?", vbYesNo)
If RUSure = vbYes Then
SavedList = True
Else
ComboBox1.ListIndex = GoBack 'This line is my attempt to change it
back.
Exit Sub
End If
End If
ListBox2.Clear
If ComboBox1.ListIndex > -1 Then
For Each v In ThisWorkbook.Worksheets("SkillsDetails").Range("A:A")
If v = Empty Then Exit For
If v = ComboBox1.Text Then
ListBox2.AddItem v.Offset(0, 1).Value
End If
Next v
End If
End Sub
 
J

JLGWhiz

It could be done with a simple loop that prevents any action being taken as
a result of the change unless the message box response equals vbYes,

RETRY:
'Code that calls the UserForm for the combobox
Private Sub ComboBox1_Change()
Ans = MsgBox("Are you sure the selection/entry is correct", _
vbYesNo, "VALIDATE")
If Ans = vbNo Then
GoTo RETRY:
End If
'Continue code execution
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top