Loading CONSTANT in Worksheet_BeforeDoubleClick code

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
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.

Code:
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:
Code:
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:

Code:
 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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

westconn1

Board Regular
Joined
Feb 20, 2009
Messages
245
i would split the 2 parts, like below, may not fix the problem, but at least can identify it

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

Watch MrExcel Video

Forum statistics

Threads
1,122,372
Messages
5,595,787
Members
414,021
Latest member
whyjaydee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top