Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cancel a combobox change
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 -- Thanks, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cancel a combobox change
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 "Mike Archer" wrote in message ... 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 -- Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combobox after change but user hits cancel to message box | Excel Programming | |||
Cancel sheet change event | Charts and Charting in Excel | |||
Disabling 'Cancel' option when saving work (Yes/No/Cancel) | Excel Programming | |||
Cancel Validation and Change Event | Excel Programming | |||
Cancel Macro is user selects 'cancel' at save menu | Excel Programming |