confirming a change in a combo box

[Paul Ponzelli]

I would like to have the user confirm any change in a combo box, and I'm
trying to use the Before Update event for this purpose, as shown in the code
below. However, there is something wrong with this code, because regardless
of whether the user selects 'Yes' or 'No', the combo box accepts the new
value.

How should I change the code below so that if the user clicks 'Yes' the
change is accepted, and if the user clicks 'No' the control reverts to the
previous value?

Private Sub InstutionType_BeforeUpdate(Cancel As Integer)
Dim response As Integer
response = MsgBox("Do you want to change the Type to the one you selected?"
& vbCr & vbCr & _
"Click 'Yes' to change the type, 'No' to keep the existing type.",
vbYesNo + vbInformation, "Institution Type changed")
If response = vbYes Then
Cancel = False
ElseIf response = vbNo Then
Cancel = True
End If
End Sub

Thanks in advance,

Paul

 

[Paul Ponzelli]

After further experimentation, I revised the code as follows:

Private Sub InstutionType_BeforeUpdate(Cancel As Integer)
Dim response As Integer
response = MsgBox("Do you want to change the Type to the one you selected?"
& vbCr & vbCr & _
"Click 'Yes' to change the type, 'Cancel' to keep the existing type.",
vbOKCancel + vbInformation, "Institution Type changed")
If response = vbYes Then
Cancel = False
ElseIf response = vbCancel Then
Cancel = True
End If
End Sub

With this code, the event serves its intended purpose for the most part,
because if the user clicks 'Yes' the change is accepted, and if the user
clicks 'Cancel' the value is not changed. However, the last value selected
remains in the unsaved control, with the display text highlighted in black.
The only way for the user to get out of it is to press the 'Esc' key.

Is there a line of VBA code I can add if the user selects vbCancel that will
have the same effect as if the user pressed the Esc key, and restore the
original value in the control?

 

[Guest]

You are doing it in the wrong event. You should be using the Not In List
event. Here is some good info on what you are trying to accomplish:
http://www.pacificdb.com.au/MVP/Code/NIL.htm

 

[Paul Ponzelli]

You should be using the Not In List event.

Thanks for your reply, Klatuu, but I can't use the Not In List event to
accomplish my purpose.

I'm bringing up a message box asking the user to confirm that they want to
make a change to a value that already exists in the combo box. I'm not
addressing what to do when the user tries to enter a value that's not in the
current Row Source.

 

[Paul Ponzelli]

I just remembered I can use SendKeys "{ESC}" at the end of the the ElseIf
block to restore the original value in the combo box.

A final question about this:

Even after cancelling the update and restoring the value to the original,
the displayed text in the combo box is still selected and highlighted in
black. I tried using SetFocus to move the focus to another control to
deselect the text in the combo box, but VBA won't permit using that method
in the event as I've written it.

Is there any way to deselect the text in the combo box at the end of the
Before Update event by either moving the cursor to the beginning or end of
the text in the combo box, or reset the focus to another form control?

Thanks again in advance.