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.

 

[dmt32]

Try this & see if does what you want.

Private Sub UserForm_Initialize()


MsgBox "got to initialize the personnel chart form"


comboPersonnelSelection.List = Sheets("Sheet2").Range("PersonnelDropDown").Value


End Sub

Note that I have returned the forms Intialize event to its default name value - you should not rename it.

Dave

 

[KBSpec]

Thank you very much. I did not know I should not rename the UserForm_Initialize subroutine.