Embedded Files
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
Page updated
Google Sites
Report abuse