dynamic named range not populating combo box list if range = single cell

I think my error is more related to my Excel formula, but it's showing up when I run my VBA (and the solution may involve VBA) so I'm posting in this forum.

I have 2 userforms that I use to enter names, Employee names and Supervisor Names. These get listed on the next available row of my "Admin Sheet", using something like:

Private Sub btnSupAdd_Click()
  Dim ws2 As Worksheet: Set ws2 = Sheets("Admin Menu")
  Dim emptyRow As Long
  With ws2
    'Determine EmptyRow
    emptyRow = WorksheetFunction.CountA(Range("$E:E")) + 1
    Cells(emptyRow, 5).Value = SupNameTextBox.Value
  End With
  Unload Me
End Sub

My add employee name routine is similar, only it goes in column B. Because I have a header row, my named range for my supervisor list is:

Formula:

=OFFSET('Admin Menu'!$E$2,0,0,COUNTA('Administrative Menu'!$E:$E)-1,1)

The "-1" is to account for row 1, the header row, otherwise a blank value shows up in my named range (it would count the header plus all the values beneath in that column, which because my offset formula starts in $E$2 and not $E$1 and the COUNTA formula counts the entire column, $E:$E). Now, this formula works, and when I open my name manager and click into the "Refers to:" box when the named range ("SupList") is selected, I get the moving selection box around cell E2 thru however names are listed below in adjacent rows in column E. Even if there's just 1 name (in E2) and E3 is blank, the name manger recognizes that one cell as my range. If there are 2 names, one in E2 and another in E3, then it recognizes those 2 but not E4. Perfect.

Here's the problem: When I try to populate a combo box with this named range, if there is only 1 cell in the range (E2), it does not read the value and does not populate with that single name! However, if there are 2 or more values (E2 and E3, plus how ever many more), it works fine and the entire named range is available. My code for populating is:

Private Sub UserForm_Initialize()
  Dim ws As Worksheet
  On Error Resume Next
  Set ws = Worksheets("Admin Menu")
  Me.cbo1.List = ws.Range("SupList").Value
  'Etc...

Similar problem with the employee list. If I change the named range formula to

Formula:

=OFFSET('Admin Menu'!$E$2,0,0,COUNTA('Administrative Menu'!$E:$E),1)

, the range then counts the blank cell after the last name, and includes that blank when the combo box is populated.

Sorry I cannot provide a sample (too much sanitizing to do) - but is should be fairly simple to understand, hopefully...

-HeyInKy

I prefer to use INDEX for Dynamic Named Ranges.

This:

Formula:

=Sheet4!$E$2:INDEX(Sheet4!$E:$E,MAX(COUNTA(Sheet4!$E:$E),2))

will give you a single (empty) cell in the DNR even if there are no entries. One or more should give you the correct Named Range. I guess.

Regards, TMS

Do you use the named range anywhere else in the workbook?

If you don't why not use code for it?

Dim rngSupList As Range

    With  Sheets("Admin Menu")
        Set rngSupList = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End If

    If rngSupList.Rows.Count = 1 Then
        Me.cbo5.AddItem rngSupList
    Else
        Me.cbo5.List = rngSupList
    End If

You would just use it in the same way, the only difference is that the listbox is only populated with one item.

Setting the value of a listbox doesn't populate it with anything, try checking out the ListCount when SupList only has one row.

Private Sub UserForm_Initialize()
Dim Nrow As Long
    On Error Resume Next
    Nrow = Range("SupList").Rows.Count
    If Nrow = 1 Then
        Me.cbo5.Value = Range("SupList").Value

        MsgBox "Did the previous line populate the listbox with the single value from SupList?" & vbCrLf & vbCrLf & IIf(Me.cbo5.ListCount = 0, "No", "Yes")

    Else
        Me.cbo5.List = Range("SupList").Value
    End If
End Sub

@Norie, your the best!

And my actual code was:

    With Sheets("Admin Menu")
        Set rngSupList = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    
    If rngSupList.Rows.Count = 1 Then
        Me.cbo7.AddItem rngSupList
    Else
        Me.cbo7.List = rngSupList.Value
    End If
    End With

Norie, inadvertently I'm sure, had "End If" instead of "End With" - and to populate the list, I had to add ".Value" to the end of "rngSupList" But I got it working

HeyInKy

Those were deliberate mistakes to promote learning, nothing to do with post-pub ramblings.