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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

, 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:

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

@Norie, your the best!

And my actual code was:

Please Login or Register  to view this content.

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.