Using Dynamic Named Ranges in VBA

[GandalfTheWhite]

I busy building a Form in VBA (Excel) and I cannot reference my Dynamic named Range in VBA (and it does not exists in my Named Ranges Drop Down on the Excel Page), yet I can reference it through Validation/List on the Excel Sheet (but want to use a Form to input the data).
Does anyone have any ideas how to activate these Dynamic named ranges (other than making it static and having someone change the Range each time the list is added to).

Thank you.

My range is =OFFSET(RefData!$D$2,0,0,COUNTA(RefData!$D195:$D293),1)

GTW.

 

[Domenic]

Let's assume that name of your dynamic named range is MyRange. To specify the dynamic named range for the RowSource property in the properties window, enter MyRange. To refer to it in code, refer to it as Range("MyRange"). By the way, dynamic named ranges do not appear in the Name Box drop down list. However, you can type the name in the Name Box to select the range on the worksheet.

 

[GandalfTheWhite]

Thanks Domenic, but I'm still struggling to declare the range, tried this but it's still not accessible in the RowSource. My Dynamic Named Range is called IncidentOwner.

Private Sub IncOwnercbo_Click()
Dim IncOwn As Range
IncOwn = Range("IncidentOwner")
End Sub

Thanks again.

GTW

 

[Domenic]

Thanks Domenic, but I'm still struggling to declare the range, tried this but it's still not accessible in the RowSource. My Dynamic Named Range is called IncidentOwner.

Private Sub IncOwnercbo_Click()
Dim IncOwn As Range
IncOwn = Range("IncidentOwner")
End Sub

Thanks again.

GTW

If you've defined the name at the worksheet level so that it's scope is 'RefData', try...

RowSource:

RefData!IncidentOwner​

VBA Code:

Worksheets("RefData").Range("IncidentOwner")​

Does this help?

 

[GandalfTheWhite]

Thanks Domenic, works like a charm now. thank you for your help, and patience.

GTW.