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]

 

[devofish]

Please disregard the second part of my question. Overlooked that I could just change the property 'style' to 2 (dropdownlist) to only show the populated items to be chosen.

 

[Norie]

To make sure the combobox is populated from 'Distribution' try this.

Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim J As Long

    '   This sub is used to fill Combobox at userform initializing
    With Sheets("Distributions").Range("A:A")
        LastRow = .Cells(.Count, 1).End(xlUp).Row

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

End Sub

 

[devofish]

It's odd. That make sense, but it still is not looking at 'Distribution'.

 

[Norie]

Can you post the exact code you tried?

 

[devofish]

Ahhh i see it now. 'Range' needed to be '.Range'. Is the purpose of the period to follow the sheet declaration? When the period was absent, it was still referencing whatever sheet was active.

I've tweaked the change event but the problem I'm having now is that when a combobox selection is made in the change event, a compile error: method or data member not found.

Private Sub ComboBox1_Change()'   Determining what is selected in combobox
    With Sheets("Distributions").Range("A:A")
            ComboBox1.SelectedDIST = .List(.ListIndex) [COLOR=#ff0000]'\\compile error here[/COLOR]
'   Getting LastRow number
        LastRow = .Cells(.Count, 1).End(xlUp).Row
'   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

 

[Norie]

Perhaps.

Private Sub ComboBox1_Change()    '   Determining what is selected in combobox
Dim idx As Long
Dim cell As Range
Dim SelectedDIST As String

    idx = ComboBox1.ListIndex

    If idx = -1 Then Exit Sub

    SelectedDIST = ComboBox1.List(idx)

    With Sheets("Distributions")

        '   Getting LastRow number
        LastRow = .Cells(.Count, 1).End(xlUp).Row

        '   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 cell
        
    End With
    
End Sub

 

[devofish]

Thank you Norie. It was erroring on LastRow so i changed it to

    With Sheets("Distributions").Range("A:A")

Now if i were to work backwords and select a listbox item to edit, I'm not sure how to populate the combobox. Any suggestions?

Private Sub ListBox1_Click()


    ' Populates controls based on user selection whereby user can update or delete item
    With ThisWorkbook.Sheets("Distributions")
        ComboBox1.Value = Me.ListBox1.List(ListBox1.ListIndex, 0) '\\error 380
        TextBox1.Text = Me.ListBox1.List(ListBox1.ListIndex, 1)
    End With
End Sub

 

[Norie]

What error were you getting on LastRow?

Also, why do you want to populate the combobox with the value selected in the listbox?

 

[devofish]

What error were you getting on LastRow?

Error 438: Object doesn't support this property or method

Also, why do you want to populate the combobox with the value selected in the listbox?

In retrospect, the combobox value actually doesn't need to change when a user selects a value in the listbox, since the values in the listbox are attributed to the value in the combobox, and the combobox selection is already made (thanks for helping me work my logic out). The value selected in the listbox (controlled by the combobox selection), is populated into a textbox that the user can edit or delete. It seems that even when I block the line that errors, the error goes to the textbox1 line.