combobox change event is firing every time in excel VBA

Question

I am getting an XML that contains data to be bound to combobox. While binding this data each time when an item is added to the combobox, its change event is fired. I want to fire change event only after data is bound and user selects any item.

Any help, code that can solve this problem?

Answer 1

Use a flag to indicate whether or not you want to handle the event;

private mblIsUpdating as boolean
...
sub addDataFromXml
   mblIsUpdating = true
   combo.additem ...
   mblIsUpdating = false
end sub

sub combo_change
  if (mblIsUpdating) then exit function
  //handle change
end sub

Answer 2

In my experience, the combobox change event only fires when changing the combobox list of items if the combobox's value is not null. If this problem is happening when you first initialize the combobox, don't assign a default value until after you fill the combobox.

If you need to change the combobox list at other times, like Alex K says, create a boolean flag to indicate if you want to ignore the change event.

I'm not clear from your question if the change event is firing once as you populate the combobox or once for each .AddItem. If it is the latter issue, then you can cut down on the number of change events by creating an array of values for your combobox and assigning it to the combobox's .List.

Here is an example with a 2-d array that is populating the combobox with the names and paths of all the open workbooks. (A 1-d array also works.)

Private Sub InitializeComboBox()
   Dim aList() As String
   Dim i As Integer, iMax As Integer

   ' build combobox list with zero-based array
   iMax = Application.Workbooks.Count - 1
   ReDim aList(iMax, 2)
   For i = 0 To iMax
      With Application.Workbooks(i + 1)
         aList(i, 0) = i
         aList(i, 1) = .Name
         aList(i, 2) = .Path
      End With
   Next i

   With Me.ComboBox1
      .ColumnCount = 3
      .ColumnWidths = "0 pt;80 pt;220 pt"
      .ListWidth = "300 pt"
      .List = aList
   End With
End Sub