combo box macro runs when using "save as"

[ctr]

I have a spreadsheet used as a form.

There is a combo box on the main page which changes the entries of
other cells
on other sheets dependant on the value chosen in the combo box.

I am using case statements to check the value of the combo box and
then change the values in other cells.

The problem is that when the user uses 'Save As' the combo box_change
macro runs and changes/deletes entries the user may have made
according to the initial rules when the macro first ran.

How do I stop this from happening?
Is there a way of checking if the user has selected 'Save As' and then
preventing the macro from running or is it simpler than this.

Any help much appreciated.

 

[Bob Phillips]

One way would be to have a global variable, set that in the BeforeSave
event if SaveAsUI is true, and test that in the combobox macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[James Martin]

Makes sense but how do I set a global variable if SaveAsUI is true?
can't seem to get it to work.
Thanks



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Create a Public variable outside of a procedure in a standard code module,
call it say NoCombo and declare as a Boolean.

In the BeforeSave event in ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
NoCombo = Not SaveAsUI
End Sub

and in your combo box macro/event, add something like

If Not NoCombo Then
'your code here
End If

You will also need to work out where in your code NoCombo gets set to False
again.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[James Martin]

Got it working now. Thanks very much for your help. Much appreciated.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!