am I thinking right?

[hitch_hiker]

I am trying to setup a 2 column combobox in a userform, the userform works, but the combobox does not load any data, the explanation on the site where I plagiarised the code from says to use 'Private Sub UserForm_Initialize()' but the code for the combobox comes up with 'Private Sub cboPart_Change()', I don't know how the cboPart_Change() got put in the code, but when I change it to 'UserForm_Initialise()' , I get a error #9 subscript our of range. do I need to change the "UserForm" to the actual name of the user form e.g. "frmProductList". or have I confused myself , and do I need to do something different?

 

[Andrew Poulsom]

No, you don't have to use the name of the UserForm. Maybe it will help if you post your code that's not working and tell us which line causes the error.

 

[hitch_hiker]

thanks Andrew,

Private Sub cboPart_Change()
'Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("'product list'") 'LookupLists = product list sheet name
For Each cPart In ws.Range("PartIDList") 'PartIDList
  With Me.cboPart
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
Next cPart

'Next cLoc

Me.cboPart.SetFocus
'End Sub
Rem .. need to change "lookuplists" to product list
    Rem ... should be private sub userform_initialize()
    
        Rem [URL]http://www.contextures.com/xlUserForm02.html[/URL]
End Sub

this is the code associated with the combobox,

this is where the debugger takes me when it errors

Sub ShowFormName()
    frmProductList.Show
End Sub

if the line reads " Private Sub cboPart_Change() " it brings up the user form with the combobox opens but there is no data in the combobox, if the line reads " 'Private Sub UserForm_Initialize() ", then I get subscript out of range

does this help ?

 

[Andrew Poulsom]

This:

Set ws = Worksheets("'product list'")

should be:

Set ws = Worksheets("product list")

ie no single quotes. You need to use the UserForm_Initialize event procedure.

 

[hitch_hiker]

Thanks Andrew,
I thought I needed the single quotes because the sheet was more than one word, need to study syntax again, thanks for you help

 

[hitch_hiker]

now I get
run-time error 1004
method 'range' of object'_worksheet' failed
the debugger takes me to

Sub ShowFormName()
    frmProductList.Show
End Sub

I will look it up , but any advise would be great

 

[Norie]

That probably means there's a problem in the initialize code.

Try putting with F9 on the first line of the code and running the form with F5.

Code execution should break where you put the breakpoint.

You can then step through it using F8 to find where the error is.

I think it might be here.

For Each cPart In ws.Range("PartIDList") 'PartIDList

 

[hitch_hiker]

thanks Norie,

I'll give that a go, and let you know

 

[Norie]

Apart from checking the code you should check that you have a named range called PartIDList.

 

[hitch_hiker]

thanks Norie,
I think I have an entire column as the range, which I don't need , so I'll check that as well