Help with combo box _Change() firing

[deucejmp]

Hi, I'm hoping someone might be able to spot a method around this issue I have. I have a userform with 3 combo boxes. (I'll call them 1, 2 and 3) The form opens when the workbook is opened using Workbook_Open() and .show in ThisWorkbook.

UserForm_Initialize() first looks up the appropriate values for the 3 combo boxes by determining the appropriate entry in combo box 1, and index/matching the cooresponding values in box 2 and 3.

On rarer occasions (if a user is accessing the workbook from a computer OTHER than their own, for example) the value in box 1 isn't appropriate. The user then clicks the drop down and manually selects from the list. After doing this, I need box 2 and 3 to recalculate the index/match for each.

Sometimes, the user will need to use box 2 or 3 to drive all of the index/matches. When this happens, box 1 and 3 need to calculate an index/match when the user changes box 2 (using the drop down), and so forth with box 1 and 2 when changing box 3.


The issue is when I include code in the _Change() subs for the individual combo boxes, the sub fires both upon first loading the form and changing the value in that particular combo box.

Yes, it makes sense because I know that UserForm Initialize() is changing all the boxes. So basically, I need to prevent the _Change() subs from firing after/during initialization.



Any ideas? Thanks for the help...


Below is code from my userform. The _Change() subs have MsgBoxes in them now for illustration.

"Click Aud"
"Click Rep" and
"Click Sta"

all fire when loading. When changing a box, only that msgbox fires (again, I know this is logical)

When I try this with appropriate code in the _Change() subs to recalucate the other index/matches, Initialize (I think) was running properly, but nothing happened after clicking a combo after that.

Please Login or Register  to view this content.

[deucejmp]

I came up with a fix. Not sure if it's the best option, but it's reasonably efficient.

I just started learning VBA a few weeks ago, so I'm steadily progressing toward the steep part of my learning curve. I didn't realize there were more events available aside from _Change(). Since _Change() fires when the value property is changed programmatically (and not just when the value is changed using the combo box, an infinite loop was created because when one change event re-writes the other two value properties, their change events fire and re-calculate the other two value properties, and so on. Even though it's calculating the same thing each time (after it runs through once) it's still processing the code. Guess I got too used to Excel's limiting of iterative calculations to a certain amount of change before it fires...


The other issue is that _Change() also fires when you click on the dropdown (before you actually change the value). Doesn't really make sense, especially when there is a DropButtonClick() event; but after thorough searching, it seems there is no way to directly test if the user has given something focus. Combining the _Enter(), _Change() and _Exit() events together in series solved both of these issues by stopping _Change() when it doen't have focus. I have two Public variables (As Boolean) dscStationFocus and dscAuditorFocus to capture focus. Focus is made True only by the _Enter() event and _Change() only runs when Focus is True, and after running it changes it back to False. If the user clicks the drop down but doesn't change the value, the _Exit() event changes the Focus back to False.

This works great, but if someone knows how to directly test for focus, I'd like to know. Below is the first code changed to incorporate the 3 events. (and I also converted all of my variables to Hungarian style notation)

Please Login or Register  to view this content.