Capturing Combobox BeforeUpdate event to run another sub

[indyman]

Greetings. I am doing an app with a form that allows for a user to enter a maximum of 2 records. It has a combobox (combobox1) with 5 values that go in a Type field - a, b, c, d, e). There are rules about whether certain values can be selected based on whether the other record value for Type. One example of a rule might be to not allow selection of 'a' if the other record has b, d, or e in the type.

My plan is to use the event like below which I am using for other controls to ensure the proper data entry (numeric, date, etc.) which works fine on other controls.

Private Sub comcbobox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        CheckFor = ActiveControl.Value
        Cancel = Not ControlValidate(CheckFor)
End Sub

A decision prompt will appear with the a Proceed and Cancel buttons. It is possible that I get into a scenario where an update to a record would require an update to the other record, putting the user in a situation where neither record could be updated without simultaneously updating the other record. The Proceed button would be an "approval" to reset the type on the other record.

My problem is that the ControlValidate function - which I am using successfully on textbox validation - does not fire unless I tab away, or select one of the buttons. I don't want to let the user move off of the combobox until a valid selection is made. Most of the event do not allow the "ByVal Cancel..." arguments. I have tried this with option buttons, but still have the same issue capturing the beforeupdate event.
I have spent many, many hours trying to consider another plan. How can I go about doing this?

I have cross posted here:

Forcing a combobox selection validate against other data

Greetings. I am doing an app with a form that allows for a user to enter a maximum of 2 records. It has a combobox (comcbobox1) with 5 values that go in a Type field - a, b, c, d, e'). There are rules about whether certain values can be selected based on whether the other record value for Type...

www.excelforum.com

 

[vw412]

I did some testing on ComboBox event firings. For a ComboBox you design into a Userform (not insert programmatically) the sequence of events that occur is:

(ComboBox gets focus)
Enter
(User clicks on DropButton)
DropButtonClick (value is same as before user clicks DropButton)
(User clicks on a selection)
Click (value is changed)
Change (value is changed)
DropButtonClick (value is changed)
(User exits ComboBox)
BeforeUpdate (value is changed)
AfterUpdate (value is changed)
Exit (value is changed)

Some comments:
If ComboBox is added to Userform at Runtime then Enter and Exit do not fire (seems these two require Design time connections to work)

To be able to perform some function on the user input to ComboBox I think you will have to capture the value on the first DropButtonClick then on the second DropButtonClick compare value now to value saved and do whatever processing needed then. Basically don't count on BeforeUpdate/AfterUpdate to work as you might expect.

I am not sure but my guess is that BeforeUpdate/AfterUpdate is not really functioning properly(buggy). If I needed to do some special processing like you want I might consider a custom ComboBox in which I would trap all the events from the standard ComboBox and create custom events which I could then trap in the Userform or other class object. That way I could make the custom ComboBox act more like I wanted.

 

[indyman]

@vw14 Thanks so much for your research and comments. I believe you are arriving at the same conclusions I have -albeit yours is far more detailed. This combobox is not added programmatically. And BeforeUpdate event must not be functioning properly. Making a custom combobox sounds beyond my ability. The only way can imagine it is to display 5 tightly stacked labels under a textbox that are hidden until a custom arrow is clicked. It sounds possible, but it is my experience labels have margins you cannot tightly control.

But in thinking about how I would manage the click events of those labels, I would still not be able to use the event arguments of ""ByVal Cancel...". You did however get me thinking differently - out of the rut of days trying of trying solutions down rabbit holes. I am going to try to capture the Change event of the combobox and write the value to a hidden textbox, and then run the "ByVal Cancel..." arguments on that textbox, which if done correctly will "'leave" the combobox until the selection is proper. I will report back.

 

[indyman]

Here is an update for future readers. I did some additional testing based on @vw412's response. This is what I found.

BeforeUpdate event on a combobox definitely is not working similar to the same event on a textbox. A simple msgbox test in BeforeUpdate does not execute until the user exits the field. Furthermore, when you exit the combobox, the BeforeUpdate runs before any exit event code.

 

[vw412]

As I have opportunity, I will work up an example of what I had in mind for a custom ComboBox that might work for you. No promise of time frame at this time.

 

[indyman]

As I have opportunity, I will work up an example of what I had in mind for a custom ComboBox that might work for you. No promise of time frame at this time.

I appreciate your willingness. If you want to do it or fun, OK. But don't do it for me. I will likely not use it. I have what I thinks is a working solution as follows. I just moved the BefroeUpdate capture to my temp texxtbox as follows. It is a fairly simple workaround to use the use the built-in Combobox functionality. And with early testing, it seems to work.

    'This code added to Combobox1 Change event will also need to be added to the Exit event.
    Me.tbTempStatus = Me.Listbox1.Value
    Me.tbSomeOtherTextbox.SetFocus 'will be hidden
    
        CheckFor = Me.tbTempStatus.Value
        Cancel = Not ControlValidate(CheckFor) 
        'True brings the user back to the combobox.  Thy have been given information and a decision in another window as part of ControlValidate.
        If Cancel = True Then
            Me.Combobox1.BackColor = rgbPink
            Me.Combobox1.SetFocus
        Else
            Me.Combobox1.BackColor = vbWhite
            Me.Combobox1.SetFocus
        End If

 

[vw412]

Sounds good. There are a couple things I want to try out to see if they work. Plus, it seems to me that a common request is how to make a control do something unique or different. I have been experimenting with custom controls and event processing for a while and thought to pass on some of what I have learned. I am glad you were able to work out a solution.

 

[indyman]

Update: I scrapped the idea of the temp textbox I mentioned above. I even tried with using option buttons as well. That works if you put them inside a frame and perform the validation on the frame exit event. While both of these did work, sometimes tunnel vision steps in. And after I ultimately stepped back and realized the temp textbox is unnecessary coding. After a little more research, I noticed the exit event also has the ByVal Cancel argument. So I moved my validation to there. It essentially acts like a before update event, not allowing them to leave the combobox without proper validation of the selected entry. )Yes I could filter the combobox to eliminate exit validation, but this allows me to show a prompt telling them what they can select and will allow them to select the appropriate update action.)

With further testing, I discovered that there is one issue with using the exit event. With a combobox it is entirely possible to exit the field leaving a blank, something I was not able to trap on exit. This can be reproduced by following the following steps:

1. Select a value.
2. Click into another control causing the exit event of the combobox.
3. Now click into the combobox (not the arrow). The originally selected value will be cleared. You can then simply click out of the combobox without selecting a value, and thus leaving it empty. I had to trap that empty value on the combobox change exit event.

All is well, and hopefully some future reader will benefit from my trials.

 

[vw412]

Thanks for the update.