Combobox_change event fires when a workbooks is saved

[Paul W Smith]

I have a problem and it is based on not udnerstanding why a Combobox_change
event fires when a workbooks is saved.

I have activex combobox on a sheet, when this box changes other actions fire
based on the selection from the control. One of these is to clear ranges in
the workbook.

All this works fine.

However when I close the workbook, the combobox_change event fires which
clear the ranges.

I am confused as to why the combobox_change event fires!!!!!

I want to save the workbook without the combobox_change event firing.

Any ideas anyone?

 

[Nick Hodge]

Paul

there is all sorts of reasons why this may fire Perhaps it has a linked cell
on the worksheet that is recalculating and therefore changing the combobox,
for example.

Try setting a breakpoint in you Workbook_Close() event and see when the
event fires by stepping through with F8.

Normally placing

Application.EnableEvents=False

will stop it, but you will want to turn them on after normal execution and
in any error handlers as you don't want Excel exiting, even by accident with
events off!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Paul W Smith]

I have now nailed it down to happening when a SaveAs... is done. When a
Save occurs the event does not fire!!!

Can this event not be fired whena SaveAs occurs?

 

[Nick Hodge]

Paul

The workbook_Beforesave() event has a SaveAsUI parameter you can check and
if it is 'SaveAs' you can either

1) Switch off events (as previously advised)
2) Use the cancel parameter to cancel the save if it's a 'SaveAs'

If SaveAsUI=True
Cancel=true

That sort of thing

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk