Userform combobox populates ListBox errors on clearing control

[devofish]

Hello masters. Below is some simple code for populating my listbox using one combobox. My issues are:

Right now, I don't have the correct sheet declaration, so how would I do this? For example, if I'm on Sheet1, the combobox shows the range for Sheet1 and not Sheet7 (Distributions).

When I save a new entry based on my combobox selection, it also errors. This also errors if I delete any value in the combobox field after activation (obviously). How do I allow the user to clear the field (by deletion) without giving an error?
Any help would be greatly appreciated!

[/COLOR][COLOR=#333333]Private Sub UserForm_Initialize()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
    '   This sub is used to fill Combobox at userform initializing
    With Sheets("Distributions").Range("A:A")
        LastRow = .Cells(.Count, 1).End(xlUp).Row
    End With

        With WorksheetFunction
        For j = 2 To LastRow
            If .CountIf(Range("A2:A" & j), Range("A" & j)) = 1 Then
                ComboBox1.AddItem Range("A" & j).Value
            End If
        Next
    End With

End Sub

Private Sub ComboBox1_Change()

'   Determining what is selected in combobox

    With ComboBox1
         SelectedDIST = .List(.ListIndex)[COLOR=#FF0000] '\\error 381: could not get list property. invalid array index[/COLOR]
    End With

'   Getting LastRow number
    With Range("A:A")
        LastRow = .Cells(.Count, 1).End(xlUp).Row
    End With

'   Filling ListBox
    ListBox1.Clear
    For Each cell In Range("A2:A" & LastRow)
        If cell.Value = SelectedDIST Then
            ListBox1.AddItem cell.Offset(0, 1)
        End If
    Next
End Sub

Private Sub cmdAdd_Click()

    Dim MyValue As Long

    With ThisWorkbook.Sheets(msSHEET_NAME)

    'check for formation name
    If Trim(Me.TextBox1.Value) = "" Then
        Me.TextBox1.SetFocus
        MsgBox "Please enter an email address."
        Exit Sub
    End If
    'check valid entries of text boxes
    If TextBox1.Value = "" Then
        MsgBox "You must enter an email address."
        Exit Sub
    End If
    If TextBox1.Value = -1 Then
        MsgBox "You must enter an email address."
        Exit Sub
    End If
     If ComboBox1.Value = "" Then
        MsgBox "You must enter a distribution group."
        Exit Sub
    End If
    If ComboBox1.Value = -1 Then
        MsgBox "You must enter a distribution group."
        Exit Sub
    End If
    End With
    'values entered are stored in sheet into first available empty row
    With ThisWorkbook.Sheets(msSHEET_NAME)
        miRowNo_Last = .Range(msTEST_COLUMN & .Rows.Count).End(xlUp).Row + 1
        .Cells(miRowNo_Last, miCOL_NO__EG) = Me.ComboBox1.Text
        .Cells(miRowNo_Last, miCOL_NO__GE) = Me.TextBox1.Text
    End With
    'sort emails alphabetically
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A2:B" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
    order1:=xlAscending, Header:=xlNo
    
    'clear the data
    Me.TextBox1.Value = ""
    Me.ComboBox1.Value = "" </code>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

 

[Norie]

As far as I can see the listbox only has one column but here you are trying to put a value from the non-existent, second column of the listbox into the textbox.

  TextBox1.Text = Me.ListBox1.List(ListBox1.ListIndex, 1)

What value do you want to put in the textbox?

 

[devofish]

Ahhh. 0 is the column. I thought that was referencing the sheet range. Now I know. It works perfectly now. Thanks again Norie!

 

[devofish]

In working with a way to work an update/delete routine I've come across something strange. Since the combobox code is intended to display name of value (e.g. Group), and listbox is intended to display unique values (email address) associated with combobox selection, the code does it's job of only displaying Groups present (there are nearly a hundred emails associated with only a dozen groups). However, if I want to edit/delete an unique email address, I think using an unique ID and matching the ID to update or delete the email instead of the group was necessary. The current code configuration only deletes or changes the first encounter with a given group selected.

So the strange part is that I am attempting to change the range reference on the initialize and change routine and it doesn't seem to be working. Since I put ID in "A", I had to move the combobox reference to "B" however it is populating "C" in the initialize and change event. I've tried swapping the ranges (B-->C and C-->B) and the offset (0 to -1) and low and behold it worked, even though initialization is pointing to B. It's driving me nuts because I can't see where I'm overlooking something since all I'm changing is A to B? I guess I could let it go since it is doing what I want, but I'm afraid that it's going to produce more problems for other routines down the line. Please tell me what I'm not seeing???

Private Sub UserForm_Initialize()        
    Const miROW_NO__HEADER  As Integer = 1
    Const miCOL_NO__ID    As Integer = 1
    Const miCOL_NO__GROUP    As Integer = 2
    Const miCOL_NO__EMAIL   As Integer = 3
    Const msTEST_COLUMN     As String = "A"
    Const msSHEET_NAME      As String = "Distributions"

    Dim SelectedDIST As String
    Dim LastRow As Long
    Dim j As Long
    Dim cell As Range
    Dim idx As Long

    With Sheets(msSHEET_NAME).Range("B:B")
        LastRow = .Cells(.Count, 1).End(xlUp).Row
        For j = 2 To LastRow
            If Application.CountIf(.Range("B2:B" & j), .Range("B" & j)) = 1 Then
                ComboBox1.AddItem .Range("B" & j).Value
            End If
        Next
    End With
End Sub

Private Sub ComboBox1_Change()

    idx = ComboBox1.ListIndex
        If idx = -1 Then Exit Sub
         SelectedDIST = ComboBox1.List(idx)
    With Sheets(msSHEET_NAME).Range("B:B")
        LastRow = .Cells(.Count, 1).End(xlUp).Row
        ListBox1.Clear
        For Each cell In .Range("B2:B" & LastRow)
            If cell.Value = SelectedDIST Then
                ListBox1.AddItem cell.Offset(0, 1)
            End If
        Next cell
    End With
End Sub