Dynamic ComboBox RowSource from other WorkBook

[Reggieneo]

Dear All,
I am constantly getting this Run Time Error 380

I opened the source, set it and even create a dynamic name range.
kindly assist.

pleas see code

Private Sub UserForm_Initialize()

Dim wkb As Excel.Workbook
Dim wks, wks1 As Excel.Worksheet

Dim Dynamic, DynamicWO, DynamicSOurce As Variant
Call OpenDWS ' the source

Set wkb = Excel.Workbooks("DWS1Carp.xlsb")
Set wks1 = wkb.Worksheets("CVerify")

copied code from '"Smallman"
wks1.Activate

Range("T2", Range("T" & Rows.Count).End(xlUp)).Name = "Dynamic"
    Me.ComboBox1.RowSource = "Dynamic"

End Sub

Thank you.
Reggieneo

 

[Belleke]

Hi try

Set rSource = Workbooks("Source.xlsx").Worksheets("Sheet1").Range("dynamic")
ComboBox1.List = rSource.Value

or (if you prefer to use RowSource)

Set rSource = Workbooks("Source.xlsx").Worksheets("Sheet1").Range("dynamic")
ComboBox1.RowSource = rSource.Address(external:=True)
End Sub

Change Source.xlsx with the name of the source workbook.
Change Sheet1 with the name of the sheet were your dynamic name range is

 

[Reggieneo]

Apologies for late reply. it works.

thank you.

 

[Belleke]

Thanks for the feedback.
Cheers