ComboBox Change Event Issue

[Ezzard]

I have an ActiveX ComboBox populated by a named range that I would like to have run a macro whenever the user changes the value. This box works fine, however, I have another separate macro that is used to update the named range that is linked to that ComboBox. This leads to the _Change() event being triggered (as well as an error being thrown) whenever I try to update the named range. The macro for the ComboBox is simply:

Please Login or Register  to view this content.

Is there a way to have that macro run only when the user selects a new drop-down item and not when the named range is updated by a separate macro?

[Norie]

You could check if the user has actually made a selection in the combobox.

Please Login or Register  to view this content.

Or you could populate the ComboBox differently, eg by using List when the sheet is activated.

[mehmetcik]

you need to stop events when running your second macro.

run your second macro

Start events.

Please Login or Register  to view this content.

Also useful are:

Please Login or Register  to view this content.

[Ezzard]

Norie,

Where would I place that bit of code? I tried doing it like so:

Please Login or Register  to view this content.

...but I still received the same error as I was before.

[Ezzard]

mehmetcik,

I added the 'Application.EnableEvents = False' to the macro that I have to update the named range, but the _Change() event still seems to be triggered.

[Norie]

What error are you getting?

[Ezzard]

It's a run-time error that comes as a result of the wrong macro being called due to _Change() event being triggered by the named range being updated. Basically, I am looking for a way to run the ComboBox macro only when the selected value is changed and not when the named range that populates its list changes. I have tried both disabling events on the macro that updates the named range as well as your suggestion and neither seem to work.

[Norie]

Disabling events using Application.EnableEvents does not affect controls.

You need to either populate the listbox in a different way so that it's not directy linked to the named range or stop the code in the change event being triggered using a global variable.

For the latter add this in a standard module.

Please Login or Register  to view this content.

Now in the change event test boolenabled.

Please Login or Register  to view this content.

Now at the start of the sub that changes the named range add this,

Please Login or Register  to view this content.

and add this at the end of the code in that sub.

Please Login or Register  to view this content.

[Ezzard]

I just tried this out and it still seems to call _Change() macro. I am not completely familiar with structuring VBA modules and such, but I have the following:

A worksheet module:

Please Login or Register  to view this content.

A module with only:

Please Login or Register  to view this content.

A module to set named range:

Please Login or Register  to view this content.

And a module that should run when the ComboBox selection changes.

I have a feeling these are not set up properly, but I'm unsure of which changes to make.

[Norie]

Oops, boolDisabled needs to be declared as Public.

Please Login or Register  to view this content.

[Ezzard]

I think I got it working finally. Thanks for all the help.