Combobox Change On Save As

[jbl25]

Hello,
I have a file with a large number of combobox controls on several
sheets. On SaveAs, every combobox change sub runs, which resets many
of the combobox values. I saw in a previous thread that this is due to
the comboboxes being linked to cells, and the recalculate before save.
Even if I turn the autocalculate off before saving, some still change,
and others change when I turn the calculations back on. I need to
store the selections from the comboboxes, so I must link them to cells.
How can I avoid them changing? Thank you!!!

 

[Bob Phillips]

You could set an event flag to bypass its action

Public fDisableEvents As Boolean

Private Sub ComboBox1_Change()
If Not fDisableEvents Then
MsgBox ComboBox1.Value
End If
End Sub

and disable the flag before the SaveAs.

 

[DM Unseen]

Maybe in Workbooks_BeforeSave event set Application.Calculation =
xlCalsulationManual ?

I'm not sure why it is preferrable to block of excel calculation,
unless this is a performance issue

DM Unseen

 

[jbl25]

Hello,
I tried disabling all workbook events right before saving, and also
tried setting calculations to manual before save, but the comboboxes
still changed. I have excel 2003 and have installed Hotfix SP1. Are
there any other solutions? Thanks very much.

 

[Tom Ogilvy]

unlink the cells and use the click event to write the values to the cells.