Making Excel Crash - ComboBox Updating RowSource ListObject

Post date: Aug 5, 2013 7:09:22 PM

A poster in one forum I frequent asked for a way to have a ComboBox display items from a list, and should the user key in something that wasn't in the list, add it to the source list.

How to Recreate the Crash

• • In a worksheet add a one column table with some values (I called in MyList with the column heading of "Test")

  • In the VBE, add a user form (I'll call it UserForm1)

  • Add two ComboBoxes to it (I called them ComboBox1 and ComboBox2)

  • Set ComboBox1's RowSource to =MyList[Test]

  • Add the following code

Private Sub ComboBox1_AfterUpdate()

Dim lr As ListRow

Dim bFound As Boolean

For Each lr In [myList].ListObject.ListRows

If lr.Range(1) = ComboBox1.Value Then bFound = True

Next

If Not bFound Then

With [myList].ListObject.ListRows.Add

.Range(1) = ComboBox1.Value

End With

End If

End Sub

• • Save the workbook

  • Display the form (UserForm1.Show).

  • Type in a new value to ComboBox1 and watch Excel crash.

The crash occurs when we add a row to MyList.

Counter Measure

We can avoid the crash by removing the ComboBox RowSource property before adding the row to MyList.

Private Sub ComboBox1_AfterUpdate()

Dim lr As ListRow

Dim bFound As Boolean

For Each lr In [myList].ListObject.ListRows

If lr.Range(1) = ComboBox1.Value Then bFound = True

Next

If Not bFound Then

ComboBox1.RowSource = ""

With [myList].ListObject.ListRows.Add

.Range(1) = ComboBox1.Value

End With

ComboBox1.RowSource = "=MyList[Test]"

End If

End Sub