VBA Link to Named Range in Another Workbook for Userform dropdown

[Jaye Cavallo]

Hello,

I am hoping to create a userform in one Excel workbook with a link to the named range in another Excel workbook. So, the first Excel file would have userform that would have a combobox that is be populated with elements of the named range in a different excel file. I have to share my Excel workbook with many co-workers. I have to share a new Excel workbook everytime the elements of the named range change. I would like the Excel file with the userform point to the named range on a shared drive, so all I would need to do is update the elements of the named range and the change would be reflected in all of the workbooks of my co-workers.

I have the below, but receive a subscript out of range error. Any help would be appreciated.

Dim rngDepartment As Range
Dim wb As Workbook

Set wb = Application.Workbooks("options.xlsm")

wb.Activate

Dim ws As Worksheet
Set ws = Worksheets("Options")

For Each rngDepartment In wb.Range("Department")
With Me.cboDepartment
.AddItem rngDepartment.value
End With
Next rngDepartment

 

[AlphaFrog]

This is a bit of a guess, but give it a try anyway...

Dim rngDepartment As Range
Dim wb As Workbook

Set wb = Application.Workbooks("options.xlsm")

wb.Activate

Dim ws As Worksheet
Set ws = wb.Worksheets("Options")

For Each rngDepartment In ws.Range("Department")
With Me.cboDepartment
.AddItem rngDepartment.value
End With
Next rngDepartment

If it doesn't work, what line of code is highlighted when it errors?

 

[Jaye Cavallo]

This is a bit of a guess, but give it a try anyway...

Dim rngDepartment As Range
Dim wb As Workbook

Set wb = Application.Workbooks("options.xlsm")

wb.Activate

Dim ws As Worksheet
Set ws = wb.Worksheets("Options")

For Each rngDepartment In ws.Range("Department")
With Me.cboDepartment
.AddItem rngDepartment.value
End With
Next rngDepartment

If it doesn't work, what line of code is highlighted when it errors?

Thank you. Your solution work as long as the files are in the same local directory. I have to figure out the path to the network drive.

 

[AlphaFrog]

Thank you. Your solution work as long as the files are in the same local directory. I have to figure out the path to the network drive.

You're welcome. Thanks for the feedback.

A couple of ideas about your network's file path:

1. Your IT Dept. could map a network drive as a local drive. As long as everyone has the same mapped drive letter for the network drive, the macro should work.
2. Try recording a macro and then open your network file. The macro may show you the recorded network file path. This doesn't always work de[ending on your type of network config.