Loading CONSTANT in Worksheet_BeforeDoubleClick code

[exceluser2007]

Hi All,

I currently have the following code that runs a macro successfuly when any cell with a named range of the form "BUTTON_OPEN_WorkbooksList_XXX" is double-clicked.

Option Explicit
'-----------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'-----------------------------------------------------------------

'    On Error GoTo ws_exit:
    Application.EnableEvents = False

    If Target.Count > 1 Then Exit Sub

'    If Not Intersect(Target, Me.Range(Create_Custom_Agent_List("BUTTON_OPEN_WorkbooksList_XXX"))) Is Nothing Then

    If Not Intersect(Target, Me.Range([B]"BUTTON_OPEN_WorkbooksList_ABC, BUTTON_OPEN_WorkbooksList_DEF, BUTTON_OPEN_WorkbooksList_GHI"[/B])) Is Nothing Then

        Call Code_to_Loop_OPEN_Workbooks_and_ListFilesMsgbox(Me.Range("OPEN_Tools_" & Right(Target.Value, 3)))

    End If

ws_exit:
    Application.EnableEvents = True

End Sub

Now this is fine, but in the bold section of the code I have the list of worksheet named range buttons that the user can double-click. As this list is likely to expand, I have created a UDF to generate this named range constant i.e. it generates the String ""BUTTON_OPEN_WorkbooksList_ABC, BUTTON_OPEN_WorkbooksList_DEF, BUTTON_OPEN_WorkbooksList_GHI".

This macro is usually called as:

Sub test()

    Create_Custom_Agent_List ("BUTTON_OPEN_WorkbooksList_XXX")

End Sub

and I have checked that it creates the required String correctly.

Now I tried passing the String output of this macro through as a CONSTANT in the above event-code as follows:

 If Not Intersect(Target, Me.Range([B]Create_Custom_Agent_List("BUTTON_OPEN_WorkbooksList_XXX")[/B])) Is Nothing Then

This throws up an error

Run-time error 1004, Method 'Range of object' Worksheetfailed

Could anyone please show how to pass this macro constant through. I would be really appreciate it.

Kind regards

 

[westconn1]

i would split the 2 parts, like below, may not fix the problem, but at least can identify it

myrange = Create_Custom_Agent_List ("BUTTON_OPEN_WorkbooksList_XXX")
msgbox myrange
 If Not Intersect(Target, Me.Range(myrange)) Is Nothing Then