Userform combobox populates different range than written code

[devofish]

I am working with an update/delete routine in my listbox, and I've come across something strange. The combobox code below is intended to display name of value (e.g. Group), and listbox is intended to display unique values (email) associated with the combobox selection. The code was doing it's job of only displaying Groups present (there are nearly a hundred emails associated with only a dozen groups) initially. However, if I want to edit/delete an unique email address, I think using an unique ID and matching the ID to the update and delete routine instead of the email was necessary, since the current delete/update code routine only works on the first encounter with a given group selected, not the actual email (and group) selected in the listbox. In order to correct this, I added another column in A and called it ID.

The strange part is that I thought attempting to change the range reference in the initialize and change routine would be a straight forward solution. Since I put ID in "A", I moved combobox reference to "B" but the combobox populates "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 that worked, but the code is referencing B! It's driving me nuts since all I'm changing is the code 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. I suppose I could just create the ID column in C and everything would work fine, but I'm trying to understand what is actually going on and why the routine is producing something different than what the code is telling it. Can someone 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

 

[JackDanIce]

Welcome to the board.

It's because you're using .Range("B:B") as part of the Parent object with so it's relative to column B. Replace your code and test with:

Private Sub UserForm_Initialize()
    
    Dim x   As Long
    Dim LR  As Long
    
    With Sheets("Distrbutions")
        LR = .Cells(.Rows.Count, 1).End(xlUp).row
        For x = 2 To LR
            If Application.CountIf(.Cells(2, 2).Resize(x-1), .Cells(x, 2).Value) = 1 Then comboBox1.AddItem .Cells(x, 2).Value
        Next x
    End With
    
End Sub


Private Sub ComboBox1_Change()
    
    Dim x   As Long
    Dim LR  As Long
    Dim str As String
    
    x = comboBox1.ListIndex
    
    If x <> -1 Then
        str = comboBox1.List(x)
        
        With Sheets("Distributions")
            LR = .Cells(.Rows.Count, 1).End(xlUp).row
            ListBox1.Clear
            For x = 2 To LR
                If .Cells(x, 2).Value = str Then ListBox1.AddItem .Cells(x, 3)
            Next x
        End With
    End If
    
End Sub

 

[Fluff]

Hia try

    With Sheets(msSHEET_NAME)
        Lastrow = .Range("B" & Rows.Count).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

Your problem was in referencing the ranges.
See this link for more information
Referring to Ranges in VBA | Excel Matters

 

[JackDanIce]

Noticed an error with my suggestion (spotted via @Fluff's code, thanks), adjusted is:

Private Sub UserForm_Initialize()
    
    Dim x       As Long
    Dim LR      As Long
    Dim arr()   As Variant
    
    With Sheets("Distrbutions")
        LR = .Cells(.Rows.Count, 2).End(xlUp).row
        For x = 2 To LR
            If Application.CountIf(.Cells(2, 2).Resize(x - 1), .Cells(x, 2).Value) = 1 Then comboBox1.AddItem .Cells(x, 2).Value
        Next x
    End With
    
End Sub


Private Sub ComboBox1_Change()
    
    Dim x   As Long
    Dim LR  As Long
    Dim str As String
    
    x = comboBox1.ListIndex
    
    If idx <> -1 Then
        
        str = comboBox1.List(x)
        ListBox1.Clear
        
        With Sheets("Distributions")
            LR = .Cells(.Rows.Count, 2).End(xlUp).row
            For x = 2 To LR
                If .Cells(x, 2).Value = str Then ListBox1.AddItem .Cells(x, 3)
            Next x
        End With
    End If
    
End Sub

 

[devofish]

Many thanks to the both! I used Fluffs and it works perfect! I see where I strayed. If I wanted to include a line(s) to sort (B) up within the initialization, how would I do that? I have been racking my brain all afternoon with different approaches and to no success. It needs to be within the initialization since I call that to repopulate the combo/listboxes after an add/delete/update to the range. I tried duplicating hte sheet reference and putting it within the With statement, but both times, it wants to sort the active sheet again.

        LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Range("B2:B" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
        order1:=xlAscending, Header:=xlYes