Hi, I have a userform with a combobox and listboxes, coded as shown below. See Excel file in attachment.
The userform is linked to a named table in worksheet "Data". The code returns an error when the named table is empty, ie. when the ListIndex = -1, ie. when the DatabodyRange is 0.
I cannot seem to figure out a way to make this code errorproof when the table does not contain any data/listrows. Do any of you know?
Option Explicit
Dim LObj As ListObject, inProcess As Boolean
Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 44 Then KeyAscii = 46
End Sub
Private Sub ComboBox1_Change()
Dim a, b
If inProcess Then Exit Sub
If ComboBox1.ListIndex > -1 Then Exit Sub
a = Application.Transpose(LObj.DataBodyRange.Value)
b = Filter(a, ComboBox1, True, vbTextCompare)
ComboBox1.Clear: CBExit.SetFocus: ComboBox1.SetFocus
If UBound(b) > -1 Then
ComboBox1.List = b
ComboBox1.DropDown
End If
End Sub
Private Sub Add(lb As MSForms.ListBox)
If ComboBox1 = "" Then Exit Sub
On Error Resume Next
lb = ComboBox1: If lb.ListIndex = -1 Then lb.AddItem ComboBox1
On Error GoTo 0
lb.ListIndex = -1
If ComboBox1.ListIndex = -1 Then
LObj.ListRows.Add.Range = ComboBox1
LObj.Range.Sort LObj.Range(1), xlAscending, Header:=xlYes
ComboBox1.AddItem ComboBox1
End If
End Sub
Private Sub Remove(lb)
Dim i&
i = lb.ListIndex
If i > -1 Then
lb.ListIndex = -1
lb.RemoveItem i
End If
End Sub
Private Sub CBAddSender_Click(): Add ListBox1: End Sub
Private Sub CBAddCC_Click(): Add ListBox2: End Sub
Private Sub CBAddRecipient_Click(): Add ListBox3: End Sub
Private Sub CBRemoveSender_Click(): Remove ListBox1: End Sub
Private Sub CBRemoveCC_Click(): Remove ListBox2: End Sub
Private Sub CBRemoveRecipient_Click(): Remove ListBox3: End Sub
Private Sub CommandButton1_Click()
Dim i&, sTxt$
i = ComboBox1.ListIndex: If i = -1 Then Exit Sub
inProcess = True
sTxt = ComboBox1: ComboBox1.ListIndex = -1: ComboBox1.RemoveItem i
inProcess = False
i = Application.Match(sTxt, LObj.DataBodyRange, 0)
LObj.ListRows(i).Delete
End Sub
Private Sub UserForm_Initialize()
Set LObj = Range("Tabla1").ListObject
ComboBox1.List = LObj.DataBodyRange.Value
End Sub
Private Sub CBExit_Click()
Unload Me
End Sub
Bookmarks