Dynamic Name range in the input range of a Combo Box (form Control)

[pratikns16]

I have dynamic name range named "Employee" defined as "=offset(Sheet1!A1,0,0,counta(Sheet1!A:A)-1,1)"
I want to make this name range as input range to my combo box. When I enter the input range for the combobox as "Employee" (without the quotes) it gives me an error "Reference is not valid". When the same name range is static and not dynamic it works totally fine. Please tell me how do I input a dynamic name range in the input range of a combo box? I have been stuck on this for hours. Thank you

 

[pluginguin]

I have the exact same problem. How to solve that?

Furthermore, I'm using a dutch version of excel, which means dutch formulas in excel but english VBA. Maybe that could also lead to issues?

 

[Norie]

Have you checked the named range is actually working?

You could do that with a simple worksheet formula like this.

=COUNT(Employee)

 

[pratikns16]

Yes, there is absolutely no problem with the name range. I have used it many times. So '=count(Employee)' is not what I want. What I need is as follows:
I have a combo box with input range as B2:B4 which includes the list of employees. Now when i click the combobox it gives me 3 options (i.e 3 employees). Now lets say if I add one more employee in B5, I want the 4th employee to automatically show up in my combobox options.
Thank you in advance

 

[Norie]

=COUNT(Employee) is to test if the range is working properly.

You should try that on a sheet other than 'Sheet1'.

 

[pratikns16]

oh Sorry! My bad!
Yes I just checked that. The name range is fine and the formula =COUNT(Employee) works fine in any sheet.

 

[Norie]

If that's the case then the combobox should work fine and be populated from the named range.

Where is the combobox located and how did you create it?

Is there anything else going on?

 

[pluginguin]

I solved it by not using the stored named range but rather defining the named range on the fly when initialising the userform.

Like this:

Private Sub UserForm_Initialize()
Blad2.Activate   'Change "Blad2" in your own sheetname, where your range is.
Dim LastCell As Range
Set LastCell = ActiveSheet.UsedRange.SpecialCells(xlLastCell) 
    Range("B2", LastCell).Name = "Employee"
    Me.ComboBoxName.RowSource = "Employee"    'Cange "ComboBoxName" to the name of your own combobox.
End Sub

If you are not using a userform but rather a combobox on a sheet, you can paste the code into the code of that sheet using:

Private Sub Worksheet_Activate()
Dim LastCell As Range
Set LastCell = RangeSheetName.UsedRange.SpecialCells(xlLastCell) 'Change "RangeSheetName" into the name of the sheet where your range is.
     RangeSheetName.Range("B2", LastCell).Name = "Employee"
    Me.ComboBoxName.RowSource = "Employee"    'Cange "ComboBoxName" to the name of your own combobox.
End sub

 

[eiopqrtuwy]

I have dynamic name range named "Employee" defined as "=offset(Sheet1!A1,0,0,counta(Sheet1!A:A)-1,1)"
I want to make this name range as input range to my combo box. When I enter the input range for the combobox as "Employee" (without the quotes) it gives me an error "Reference is not valid". When the same name range is static and not dynamic it works totally fine. Please tell me how do I input a dynamic name range in the input range of a combo box? I have been stuck on this for hours. Thank you

I had the same issue, it's the references:

This won't work:
=offset(Sheet1!A1,0,0,counta(Sheet1!A:A)-1,1)
This will:
=offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A)-1,1)

I realize this is an old thread but hopefully I can help the next person who finds this thread through google.

 

[realPrincessApril]

That's great @eiopqrtuwy. How can we remove blank rows and zero values from the second formula you mentioned?