EXCEL COMBOBOX - VBA - executed when workbook is loaded

Question

I've defined an embedded ActiveX comboBox on my worksheet page, and this ActiveX Combo has associated a list and it executes a macro when some value is selected.

This comboBox selection comes from a table located on the same worksheet. And Associated macro has this "Private Sub ComboBox1_Change()" definition.

But each time that I loads the worksheet the macro code associated to the COMBOBOX is executed.

How can I avoid that combobox code is executed at startup? I've tried to disable events on workbook but it doesn't works.

Regards

Answer 1

I know what you mean. It happens with me sometimes as well. This is what I do in this case. Use these codes

In a module

Public DoNotRun As Boolean

In ThisWorkBook Code Area

Private Sub Workbook_Open()
    DoNotRun = True

    '
    '~~> Rest of the code if any
    '
End Sub

In the ComboBox

Private Sub ComboBox1_Change()
    If DoNotRun = True Then
        DoNotRun = False
        Exit Sub
    End If

    '
    '~~> Rest of the code
    '
End Sub

NOTE: Do remember to set DoNotRun = False somewhere after you open the workbook. Else the _Change() event will not fire if you try to change an entry in the ComboBox immediately after you open the workbook.

Alternative

Use ComboBox1_Click() instead of ComboBox1_Change() if you can.

Answer 2

Nice trick, thanks.

But I've tried to set it, but it doesn't worked as expected. To track what happenned I've added some message popups to see how it works, and I've realised that for some reason this code is executed in different order. What I've see is that the first executed code was the ComboBox, and then the code inside in "ThisWorkbook"

I've set as you explained, adding the Workbook_open at "Thisworkbook" code and a public variable definition.

I've also revised these guidelines for macros at startup http://office.microsoft.com/en-us/excel-help/running-a-macro-when-excel-starts-HA001034628.aspx but ....

Answer 3

At least I've found the reason of this strange behavior.

I had set the fillrange property directly on the COMBOX setting properties, and not programmatically using VBA

When I've removed this property Workbook_load function run at first again.. and ComboBox macro was not activated before any other.

Now I'll have to find the another way to load data on the ComboBox..

Regards