CODEWITHSUNDEEP
excelvbacomboboxonchange
user2571510
user2571510

Reputation: 11377

VBA: How to trigger event when selected value in combobox changes

I am new to VBA and would like to trigger a simple change event when the selection in a combobox (within a UserForm) changes.

I tried both Private Sub myCombo_AfterUpdate() and Private Sub myCombo_Change() but in both cases nothing happens when I select a different value in the combobox.

Is this the wrong approach for comboboxes ?

My Sub starts as follows as I want to compare the currently selected item in the dropdown with the value in a table:

Private Sub myCombo_AfterUpdate()
    If Me.myCombo.Value = Worksheets(8).Range("A4") Then
    'do stuff
End Sub

Many thanks for any help with this, Tim.

Upvotes: 5

Views: 55238

Answers (1)

Albina
Albina

Reputation: 1987

By default, when new sheets are created, namings are represented like this: Sheet1, Sheet2, Sheet3, etc. They are numbered in the creation order.

The 'functions' Worksheets(1), Worksheets(2) can be used to get sheet by index. This index can be either number of sheet name:

Use Worksheets (index), where index is the worksheet index number or name, to return a single Worksheet object.

It basically means that Worksheets(i) returns the current i-th sheet of your workbook (if i is an integer).

The problem is that the sheet numbers (e.g. '1' in Sheet1 and Worksheets(1)) are not necessarily the same. They are the same by default if sheets are created one by one and the order is unchanged. However, there may be multiple cases when the numbers do not match.

Therefore it is strongly recommended to get worksheets by name and not by number, i.e.:

Worksheets("Sheet1")

and not

Worksheets(1)

Also, there is still an option to refer to the sheet as the object (Sheet1).

P.S. An example how to reproduce the worksheet index mismatch

  1. Create at least 2 sheets in MS Excel with default names. In our example. Sheet1 and Sheet2 are created.
  2. Add a Button object on a Sheet1.
  3. Transfer Sheet2 to the left to make it the leftmost page (as it showed on the screenshot).
  4. Add a macros to the Button object on Sheet1 and add a breakpoint to the macros. Also, using Add Watch... command add variables to the debugging section
    • Worksheets(1).CodeName
    • Worksheets(2).CodeName
  5. If you click on the Button object and go to the VB code window, in the Watches window you'll see that
  • Worksheets(1).CodeName = "Sheet2" (the leftmost Sheet)
  • Worksheets(2).CodeName = "Sheet1" (the second Sheet)

Screenshot

Upvotes: 1

Related Questions