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.

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)

"ctr" wrote in message
om...
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.

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!

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" wrote in message
...
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!

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!