Cancel a combo box change with VBA

[Guest]

Hi,

I have a combo box from which I'd like to cancel the change depending on the
answer selected from a msgbox. It goes something like that...

The user changes the combo box value.
The system asks the user to confirm he wants to change the value.
The user says No.
The combo box is left untouched.

There's more to it but I simplified it so that it's easier to explain.

Can anybody point me in the right direction as to how I should proceed to
accomplish this with VBA?

Thanks in advance!

 

[Douglas J. Steele]

Assuming it's a bound combo box, put code like the following in its
BeforeUpdate event:

Private Sub MyComboBox_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you really want to change the value?", _
vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
Cancel = True
Me.MyComboBox.Undo
End If

End Sub

 

[Ken Snell \(MVP\)]

Use the combobox's BeforeUpdate event to do this (note my use of a generic
name for the combo box):

Private Sub ComboBoxName_BeforeUpdate(Cancel As Integer)
If vbNo = MsgBox("Do you want to change this value?", _
vbYesNo+vbDefaultButton2+vbQuestion, _
"Confirm Change") Then
Cancel = True
Me.ComboBoxName.Undo
End If
End Sub

 

[John W. Vinson]

Hi,

I have a combo box from which I'd like to cancel the change depending on the
answer selected from a msgbox. It goes something like that...

The user changes the combo box value.
The system asks the user to confirm he wants to change the value.
The user says No.
The combo box is left untouched.

There's more to it but I simplified it so that it's easier to explain.

Can anybody point me in the right direction as to how I should proceed to
accomplish this with VBA?

Thanks in advance!

You can (but perhaps shouldn't, see below!) use VBA code in the combo's
BeforeUpdate event, such as

Private Sub cboMyComboBox_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
iAns = MsgBox("Are you sure you want to change it?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End Sub


Just a suggestion: you'll find that this prompt will get to *really* annoy
your users. "Look, durn it, I changed it because I WANTED to change it! Why
are you asking me!?" In practice, the user will mindlessly click the Yes
button and grumble about your wasting their time. Why do you feel that the
confirmation is needed?


John W. Vinson [MVP]

 

[Guest]

Thanks to both you and Ken!

Assuming it's a bound combo box, put code like the following in its
BeforeUpdate event:

Private Sub MyComboBox_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you really want to change the value?", _
vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
Cancel = True
Me.MyComboBox.Undo
End If

End Sub