Excel VBA - conditional combo boxes

whitedog
whitedog used Ask the Experts™
on
I have two activeX combo boxes in an excel sheet where the selection in the first box determines the values displayed in the second box.  When a user clicks combo box 1, the vba code monitors the selection and then populates the values of combo box2 and drops down combo box2.

My problem is that when I save the excel workbook under another name this triggers the click event of the combo box1.  So I lose the selected value in combobox2 and am presented with the drop down menu with nothing selected.

I tried deactivating combobox1 after selecting a value for combobox2 but the same thing happened when using "Save As".
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
Hello whitedog

guess the sheet is getting recalculated on saving

try something like

'first set this calc option to false
Application.CalculateBeforeSave = False

'then do your save

'restore the calc option to true again
Application.CalculateBeforeSave = True

HAGD:O)Bruintje

Author

Commented:
No that doesn't do it.

The vba code is still triggered when the "Save As" operation completes.

Short Term Action: I've disabled the "Save As" function and can "Rename" the files via Explorer.  Suppose I could put a workbook "Rename" button on the form if the users complain.

If I find the time, I'll have to disect the "Save As" functions.

Author

Commented:
Problem Solved

Under Tools/Options/Calculation tab, all I had to do was set the Calculation mode to "Manual" and uncheck the recalculate before save checkbox.

thanks for the assistance.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- PAQ'd and points refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

twalgrave
Cleanup Volunteer
User resolved; points refunded and question closed.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial