Populate userform combobox with dynamic range from different workbook

[chayes55]

Ok, I'm going to do my best to be short and sweet, but I've explored so many options I feel crazy right now...

I have successfully used named dynamic ranges to populate the comboboxes in my userform, and they work beautifully...until I try to use one from a different workbook. I've tried using the vba to open the workbook first, just have the workbook open, using full file location and what feels like a million other things.

So, I used this as the "Referenced Cells" in the named range:

=OFFSET('GLSPECS!$A$2,0,0,COUNTA('GLSPECS'!$A:$A)-1,1)

And this as the code to initialize the userform and it works beautifully:

Private Sub UserForm_Initialize()


'Populate Glazing Type Combo Box
  Dim rngGLTYPE As Range
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("GLSPECS")
  For Each rngGLTYPE In ws.Range("GlazTypeList")
  uf_NewDoor.cb_GlazingType.AddItem rngGLTYPE.Value
 Next rngGLTYPE

End Sub

But now when I name a range in a different workbook for a different combobox:

=OFFSET('Rails&Hinges'!$A$2,0,0,COUNTA('Rails&Hinges'!$A:$A)-1,1)

And use this code:

'Populate Hinge Combo Boxs
   Dim rngRailsHinges As Range
   Workbooks.Open ("I:\Commercial Projects\2-TEMPLATES\Hardware Cost List.xlsm")
   Set ws = Workbooks("I:\Commercial Projects\2-TEMPLATES\Hardware Cost List.xlsm").Worksheets("Rails&Hinges")
   For Each rngRailsHinges In ws.Range("RailsHinges")
   uf_NewDoor.cboTopRailHinge.AddItem rngRailsHinges.Value
   Next rngRailsHinges

It fails and says :
"Run-time error '9':
Subscript out of range"

Any and ALL feedback, advice, tip or tricks would be so, so, SOOO appreciated!

 

[timfritsch]

I'm guessing you are getting the run time error 9 because the workbook with the combobox is not active when you are trying to add the list items. After opening the Hardware Cost List, activate the workbook with the GLSPECS sheet and see if that fixes the error.


Tim

 

[chayes55]

I'm guessing you are getting the run time error 9 because the workbook with the combobox is not active when you are trying to add the list items. After opening the Hardware Cost List, activate the workbook with the GLSPECS sheet and see if that fixes the error.


Tim

hmm...did I activate it correctly...still error 9 :/

'Populate Hinge Combo Boxes
   Dim rngRailsHinges As Range
   Workbooks.Open ("I:\Commercial Projects\2-TEMPLATES\Hardware Cost List.xlsm")
   Set ws = Workbooks("I:\Commercial Projects\2-TEMPLATES\Hardware Cost List.xlsm").Worksheets("Rails&Hinges")
'new line
   Workbooks("I:\Commercial Projects\2-TEMPLATES\JOB LOG - Template.xlsm").Activate


   For Each rngRailsHinges In ws.Range("RailsHinges")
   ufNewDoor.cboBotRH.AddItem rngRailsHinges.Value
   Next rngRailsHinges

Can you think of anything else?
Thank you so much for taking the time to help!!!

 

[timfritsch]

Well, this worked for me:

Private Sub UserForm_Initialize()
 Dim masterWb As Workbook, sourceWb As Workbook, sourceRng As Range
  
  Set masterWb = ThisWorkbook
  Application.Dialogs(xlDialogOpen).Show
  Set sourceWb = ActiveWorkbook
  
  For Each sourceRng In Range("table")
    uf1.cb1.AddItem sourceRng.Value
  Next
End Sub

masterWb has the userform
sourceWb contained a named range ("table") with the values to add to the combobox
uf1 = userform
cb1 = combobox

Exactly which line are you getting the run time error on?


Tim

 

[chayes55]

When I press the Debug button it points to the code that opens the userform

Sub NewDoor()


'it highlights this next line here

ufNewDoor.tbDrTyp.SetFocus
 
 With ufNewDoor
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
 End With


End Sub