Thread: VBA ComboBox_Change firing multiple times

Hi All,

I've created an excel document with many defined names.

I have created some comboboxes users can use to select some values. I use defined names as ListFillRange for the comboboxes.
I also have a linkedcell for each combobox (in the A-column, which is unprotected but hidden from the users).
The whole sheet the user sees, is protected (except for the A-column, but that one is hidden).
The sheet also does not show any headings and no grid. Just for your information...

The problem that I have is the following:

I have many lines of VBA code written. All code is working fine, except for the following: (in the Microsoft Excel Objects, in the correct sheet (so not in module, nor in class module))

Private Sub ComboBoxCountry_Change()
  If UserSheet.ComboBoxCountry.ListIndex < 0 And UserSheet.ComboBoxCountry.Value <> "" Then
    UserSheet.Range("ComboBoxCountryLinkedCell") = "=A11" 'In cell A11, I have a reference to a defined name, namely "=DefinedNameCountryStandard"
  End If
End Sub

The sub mentioned above should reset the combobox entry to the standard country if the user changed it to an unwanted value.
However, the change fires multiple times, namely 3 times!
My explanation for this is the following:

First time it fires: change to "=A11",
Second time it fires: change to the value of A11, namely "=DefinedNameCountryStandard"
Third time it fires: change to the value of DefinedNameCountryStandard, namely "The Netherlands".

I think it's weird that the combobox_change() fires three times, while I expected it to fire only once.

I tried using Application.EnableEvents = False, but that doesn't work. After some searching on Google I've learned that it doesn't work on user input fields, such as ComboBoxes.
Then I tried building a variable in my VBA code, but I couldn't get that to work. I tried using a Cell "A20" as a boolean (True/False) and using it like this:

Private Sub ComboBoxCountry_Change()
  Dim enableCountryChange As Boolean
  enableCountryChange = UserSheet.Range("A20").Value
  If enableCountryChange Then
    If UserSheet.ComboBoxCountry.ListIndex < 0 And UserSheet.ComboBoxCountry.Value <> "" Then
      UserSheet.Range("A20").Value = False ' set enableCountryChange to FALSE
      UserSheet.Range("ComboBoxCountryLinkedCell") = "=A11" 'In cell A11, I have a reference to a defined name, namely "=DefinedNameCountryStandard"
      UserSheet.Range("A20").Value = True ' set enableCountryChange to TRUE
    End If
  Else
    'EnableCountryChange set to FALSE, so exit sub without continuing
    Exit Sub 'Tried it without the "Exit Sub" call also
  End If
End Sub

This code sometimes works fine in my excel document, but sometimes it doesn't and I can't find out why (I'm debugging quite a lot the past few days).
The sub combobox_change keeps firing multiple times, and same goes for some - if not all - other comboboxes I'm using.
Because of security-issues I can't post the file, but I've provided you with all the information I think is necessary.

If anyone else knows a workaround or a solution to my problem, please tell me...

Thanks,

Percy Dobbelsteyn

Oops!

You are bound to receive SERIOUS REPRIMANDS from the Forum's Moderators for not playing by the rules!

Please read the RULES FIRST, and then quickly, rectify your Post.

You need to add code tags to your post before we can tell you the answer.

[code]
your code goes here
[/code]

@ OEGO,

Hi OEGO, thanks for backing me up.

B.T.W. I like the abreviation OEG0!

Note where the "O" and the Zero should be! LOL

I like mine better.

Since you insist, I have to accept it as such! Zero EGO ?

Maybe that's why I like you.

I think you will find that the issue is your links to the sheet with ListFillRange. If you populate the combo boxes in code (you can just assign the value of the ranges to the List property), you should not have the issue.