vba for combobox row source from different workbook

[Rodrigues]

Hi All
Please, can someone tell me that, it's possible to have a vba code to set ComboBox row source property, from another excel file, preferably with source file closed (if possible at all).
The plan is, have multiple userforms (with same name) on separate folders with all combobox's pointing the row source to a master data excel file with name ranges setup, to avoid sharing the userform, which as far as I understand is not ideal to do do.
Example@

Path: C:\Userform\MasterData.xlsx

C:\Userform\Folder A
Userform1 - ComboBox1 - row source c:\masterdata\worksheet1"name range A"
Userform1 - ComboBox2 - row source c:\masterdata\worksheet2 "name range B"
Userform1 - ComboBox3 - row source c:\masterdata\worksheet3 "name range C"

C:\Userform\Folder B
Userform1 - ComboBox1 - row source c:\masterdata\worksheet1"name range A"
Userform1 - ComboBox2 - row source c:\masterdata\worksheet2 "name range B"
Userform1 - ComboBox3 - row source c:\masterdata\worksheet3 "name range C"

Many thanks in advance.
R

 

[Debaser]

You can't do that.

I would suggest that you use ADO to query the data from the source file and then load it into the comboboxes from there.

Sub GetNamedRangeData()
' Sample demonstrating how to return a recordset from a named range in a workbook
' NOTE: this will not work with a dynamic named range

   Dim cn As Object, strQuery As String, rst As Object
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=C:\your source file.xlsx;" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=No;"""
      .CursorLocation = 3
      .Open
   End With
   strQuery = "SELECT * FROM [named range];"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, 1, 3
   Me.Combobox1.Column = rst.Getrows
   rst.Close
   cn.Close
End Sub

 

[Rodrigues]

Debaser thank you, will have a go.
R