Use Named Range to Populate Userform Combobox

[KBSpec]

Hi All,

I've been combing the web and trying to work this out on my own, but I have to admit I need your help. I've created a named dynamic range that I have successfully pulled into a combobox in a worksheet. What I really want to do is use it in a userform combobox in the same workbook. I just can't seem to figure out the syntax. Please help.

The Workbook is LAL Test Trending
The dynamic range is called PersonnelDropDown, the source is on a worksheet named Sheet2
The userform is named frmPersonnelCharts
The userform combobox is called comboPersonnelSelection

This is the most recent version of my code trying to use PersonnelDropDown for comboPersonnelSelection:

Sub buttonPersonnelCharts_Click()
'Open frmPersonnelCharts when the Sheet2 cmdPersonnelCharts is clicked
Call frmPersonnelChart_Initialize
frmPersonnelCharts.Show
End Sub

Private Sub frmPersonnelChart_Initialize()

MsgBox "got to initialize the personnel chart form"

comboPersonnelSelection.RowSource = Workbooks("LAL TEST Trending").Names("PersonnelDropDown").RefersToRange

End Sub

I get the message that the code got to initialize the personnel chart form but it crashes on the combobox code RowSource assignment.

Please let me know how to fix this.

Thank you for any and all help.

 

[ryansclev]

So the combo box will always refer to the PersonnelDropDown Range, right? You can set the rowsource for the combobox in the properties window in VBA, just go to view --> Properties Window. Then click on your ComboBox and in the RowSource Property enter =PersonnelDropDown.

 

[KBSpec]

I get the error:

Microsoft Visual Basic for Applications
Could not set the RowSource property. Invalid property value.

The equation to create the named range is:
=Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$9,Sheet2!$F$2) where F2 calculates the end of the dynamic range.

The range is PersonnelDropDown with the above equation in the Name Manager. Is it something other than a range? Then why does it work for the combobox I have directly in the Charts worksheet?

Very confused.

 

[ryansclev]

Check the scope of your named range. If its not workbook, delete it and recreate it with a workbook scope and try it again.

 

[KBSpec]

The scope is workbook. Anything else I can check?

Thanks so much.

 

[ryansclev]

The only other thing I can think of is to make sure you are spelling it the same in the rowsource as you are in the name manager.

I created the named range in a new workbook using the same formula you have and the combobox works. Not sure what else it could be.

 

[KBSpec]

Thank you so much for all your help. I made another named range - PersonnelList - with the same equation. And that works just fine. I never saw anything misnamed, but I'm glad you gave me the idea to try another named range.

Thanks again for all your help.

 

[ryansclev]

Glad you got it working!