Call Userform and use range from different workbook

Question

I'm trying to let the command boxes on a userform populate with named ranges that are called from a different workbook. I have a main sub that calls different subs and userform code.(code as below)

Currently, the userform loads but the combo boxes are empty. I think the problem is with calling the main Sub and then initializing the userform sub.

    Load FrmVendor
    FrmVendor.Show

Private Sub FrmVendor_Initialize(myNamedRangeDynamicVendorName As Range, myNamedRangeDynamicVendorCode As Range)

'add column of data from spreadsheet to userform ComboBox
    cboxVendorName.RowSource = myNamedRangeDynamicVendorName.Address(external:=True)
    cboxVendorCode.RowSource= myNamedRangeDynamicVendorCode.Address(external:=True)
    cboxVendorCode.ColumnCount = 2

This is parts of the code to show where I get my named Ranges, all the userform settings and then just the part of the main macro that calls the userform.

Option Explicit
Private m_Cancelled As Boolean


Sub NamedRanges(wb As Workbook, wSh As Worksheet)
    Dim myNamedRangeDynamicVendor As Range
    Dim myNamedRangeDynamicVendorCode As Range

'declare variable to hold defined name
    Dim myRangeNameVendor As String
    Dim myRangeNameVendorCode As String

'specify defined name
    myRangeNameVendor = "namedRangeDynamicVendor"
    myRangeNameVendorCode = "namedRangeDynamicVendorCode"

'Vendor Name range
    With wSh.Cells

        'find last row of source data cell range
        myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        'specify cell range
        Set myNamedRangeDynamicVendor = .Range(.Cells(myFirstRow, "A:A"), .Cells(myLastRow, "A:A"))

    End With

 'Vendor Code range
    With wSh.Cells

        'specify cell range
        Set myNamedRangeDynamicVendorCode = .Range(.Cells(myFirstRow, "B:B"), .Cells(myLastRow, "B:B"))

    End With

'create named ranges
    ThisWorkbook.Names.Add Name:=myRangeNameVendor, RefersTo:=myNamedRangeDynamicVendor
    ThisWorkbook.Names.Add Name:=myRangeNameVendorCode, RefersTo:=myNamedRangeDynamicVendorCode
End sub



' Returns the cancelled value to the calling procedure
Public Property Get Cancelled() As Boolean
    Cancelled = m_Cancelled
End Property

Private Sub buttonCancel_Click()
    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True
End Sub

' Hide the UserForm when the user click Ok
Private Sub buttonOk_Click()
    Hide
End Sub

' Handle user clicking on the X button
Private Sub FrmVendor_QueryClose(Cancel As Integer, CloseMode As Integer)

    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True

    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True

End Sub

Private Sub FrmVendor_Initialize(myNamedRangeDynamicVendorName As Range, myNamedRangeDynamicVendorCode As Range)

'add column of data from spreadsheet to userform ComboBox
    cboxVendorName.RowSource = myNamedRangeDynamicVendorName.Address(external:=True)
    cboxVendorCode.RowSource= myNamedRangeDynamicVendorCode.Address(external:=True)
    cboxVendorCode.ColumnCount = 2

End Sub


Sub Main Macro
'
'
'
    Call NamedRanges(wb, wSh)

    ' Display the UserForm
    Load FrmVendor
    FrmVendor.Show


    ' Clean up
    Unload FrmVendor
    Set FrmVendor = Nothing

Any help will be appreciated thanks!

Answer 1

change :

Load FrmVendor
FrmVendor.Show

to:

With New FrmVendor
    .Tag = myRangeNameVendor & "|" & myRangeNameVendorCode
    .Show
End With

and you can't change the signature of the Initialize event which shall stay without parameters

Private Sub FrmVendor_Initialize()

    'add column of data from spreadsheet to userform ComboBox
    With Me
        .cboxVendorName.RowSource = ThisWorkbook.Names(split(.Tag,"|")(0)).Address(external:=True)
        .cboxVendorCode.RowSource= ThisWorkbook.Names(split(.Tag,"|")(1)).Address(external:=True)
    End With
    cboxVendorCode.ColumnCount = 2
End Sub

And you should revise:

ThisWorkbook.Names.Add Name:=myRangeNameVendor, RefersTo:=myNamedRangeDynamicVendor
ThisWorkbook.Names.Add Name:=myRangeNameVendorCode, RefersTo:=myNamedRangeDynamicVendorCode

in order to use Range Addresses instead of pure Ranges (BTW those Ranges are never set)

edit:

make sure you have both myRangeNameVendor and myRangeNameVendorCode "visibile" in your Main Macro() sub: yuy may declare them as Public constants of type String, and have them available all over your project

Public Const myRangeNameVendor As String = "namedRangeDynamicVendor"
Public Const myRangeNameVendorCode As String = "namedRangeDynamicVendorCode"

Sub Main Macro
'
'
'
    Call NamedRanges(wb, wSh)

    With New FrmVendor
        .Tag = myRangeNameVendor & "|" & myRangeNameVendorCode
        .Show
   End With

   ...

And so NamedRanges would be :

Sub NamedRanges(wb As Workbook, wSh As Worksheet)
    Dim myNamedRangeDynamicVendor As Range
    Dim myNamedRangeDynamicVendorCode As Range


    'Vendor Name range
    With wSh.Cells

        ...

Answer 2

With Help from @HTH answer above to make changes to the range names, I still had a problem with filling the comboboxes. From another thread I posted, the problem was solved with a combination of @HTH and @CDP1802 answers.

Link: Userform Comboboxes not populating with external data range

Main code that needed to change

• Moved all the Userform subs and procedures to the UserForm Code
• Removed the Initialize code
• Adjusted the named ranges as per @HTH answer above
• Changed the .tag code for setting the combobox addresses

The .tag code would normally work but caused problems for me, so had to change it to the following (full answer and description in the link provided above, answer by @CDP1802)

    ' Display the UserForm
With New FrmVendor
    .cboxVendorName.RowSource = wb.Names(myRangeNameVendor).RefersToRange.Address(external:=True)
    .cboxVendorCode.RowSource = wb.Names(myRangeNameVendorCode).RefersToRange.Address(external:=True)
    .Show
    VendorName = .cboxVendorName.Value
    VendorCode = .cboxVendorCode.Value
End With