Combo Box Changing on Exit or Open

[ker9]

Hi, I have a combo box, value linked to a cell. Save workbook. Exit. (These are ActiveX boxes on worksheet.)
Re-Open, can see value in box as saved
Excel is running something prior to being able to work in sheet, and then value disappears.
Has to do with a change event I think, but nothing should run automatically on open.
There are no on open/on close workbook events.

How can I track this down? Nothing should run on open. It may have to do with a change event, but nothing has been changed.

Any debugging suggestions would be most welcome.

Can I EnableEvents false on open? How to change back?

Update - debugging - on exit it runs the change event on a different box (ClientType) that affects the one losing value (Fees), but I don't know what would cause that. What would trigger a combo change on exit??? It doesn't change the value of ClientType, only on Fees

Thanks

[Leith Ross]

Hello ker9,

Since you have the control linked to a cell on the worksheet, are there any formulas that affect that cell? If the cell's contents are changed, the control will fire a change event.

[ker9]

Hi, thanks for response. Nothing is linked to that cell except the combo box.
It appears to be firing a change event on cmbClientType on exit. This is what's changing the cmbFee box. How can I track down what is firing a change event on exit?
I tried disabling the box before close and then enabling on open but that didn't work.

[Leith Ross]

Hello ker9,

I can help help you trouble this faster if post a copy of the workbook. Is that possible?

[ker9]

Workbook is huge with a lot of proprietary data

I found this on the web but I'm not sure how to use it or if it's appropriate:


>Try using a public boolean variable selectively to disable the ComboBox
> code.
>
> At the top of a standard module:
>
> Option Explicit
>
> Public blDisable As Boolean
>
> Then, in the sheet module:
>
> '=============>>
> Private Sub ComboBox1_Change()
> If blDisable Then Exit Sub
> 'Your code
> End Sub
> '<<=============
>
> When you need to disable the ComboBox1_Change code, assign a value of True
> to the variable.

[Leith Ross]

Hello ker9,

Can you post the code you have?

[ker9]

On exit it runs the change for the combo box although nothing has changed.
I set break points at the beginning of every sub in the entire workbook to see what was running first on exit and it is only the Combo Change routine. There are no other combo boxes on any other sheets.

(and I learned that breakpoints are then cleared either on exit or open, so it's a total pain to reset them all)

On open it does something, but I can't catch it
Is there any way to open in debug mode?

[ker9]

I'm trying to clean it up to post it

[ker9]

It's almost 2mb so it won't let me upload it - any suggestions?

[Leith Ross]

Hello ker9,

Did you try zipping the file to reduce its size?

[ker9]

Ok, so I'm so frustrated I'm getting stupid

[ker9]

The problem is on the Approval sheet, for whatever reason, it's launching the change combo code of the first combo box on exit.

[Leith Ross]

Hello ker9,

I have tried many ways to reduce the error, but haven't been able to. If I clear the combo box and save the file, the combo box is clear when the file opens. I would try setting the calculation mode to manual and reopen the workbook to rule out any formulas or saved links that could be causing a problem. Beyond that, I have no other ideas.

[ker9]

Thank you for trying. I believe setting a boolean to true (or false) and then changing it may be the answer but I am not sure how to do it yet.
It launches that change because of something it's doing automatically. I have to figure out how to capture the event to figure out what it is.

[ker9]

The problem was that my combo box value is linked to a cell and Excel is calculating on open/close, which is launching the combo change event. At the beginning of the sub I insert Application.EnableEvents = False and change it back to true at the end of the sub. This appears to have fixed the problem.
THE ABOVE DID NOT WORK (it appeared to work but on heavier testing it did not work.)


REVISED:
FINAL RESOLUTION:
I had to turn a "flag" on or off as follows:
At the top of a module:

Please Login or Register  to view this content.

The switch starts out as false

You can use this code to check the status of the switch:

Please Login or Register  to view this content.

In my application, the ClientType combo box had a Change event and no other events. I added a DropButtonClick event to change the switch to True.

Please Login or Register  to view this content.

In the ClientType change event I added this code as the first thing:

Please Login or Register  to view this content.

The above is what prevents the code from running when you save/exit/open and Excel automatically calculates.

Also in the change event, I switched to false at the end of the code (so if the Click event turned it true, the change event returns it to false:

Please Login or Register  to view this content.

In the Workbook events, I made sure it went to false BeforeClose, on Open, on Save.

The only time it is True is when the user clicks the down arrow of the combo box. If they do not change the combo box after the click the flag remains True, however, it reverts to false on exit/save/open. (I probably don't need it in all 3 places but it feels like insurance to me.)

The difficulty was determining where and how to turn it to True. OnFocus/LostFocus is a possible option.

Hope this can help someone else

[James Lex]

Thanks for posting the great solution! It worked perfectly for me. This solved my problem of the screen flickering wildly on open of my workbook because of all the code embedded in several drop-downs.