Prev: SELECT PART OF A TEXT IN A CELL
Next: If date more than
From: Mike Archer on 7 Apr 2010 14:00
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
From: JLGWhiz on 7 Apr 2010 15:17
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" <MikeArcher(a)discussions.microsoft.com> wrote in message
news:5F7207B9-3DEB-4384-8CE4-E9AB3A5D68B4(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: SELECT PART OF A TEXT IN A CELL
Next: If date more than