Module Script works. Double click post does not work

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,510
Office Version
  1. 2021
Platform
  1. Windows
I have this script which works when run as a module script by pressing a button:

VBA Code:
Sub Range_Name()
'Modified 10/16/2020 3:55:51 AM  EST
Dim sn As String
Dim ans As String
ans = ActiveCell.Value
sn = Range(ans).Worksheet.Name
Sheets(sn).Range(ans).Copy Sheets(1).Range("B11")
End Sub

But then I have this same script as a double click script but it causes a error when I double click on a cell with a proper named range in the cell I double click on:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim sn As String
Dim ans As String
ans = Target.Value
sn = Range(ans).Worksheet.Name
Sheets(sn).Range(ans).Copy Sheets(1).Range("B11")

End Sub

I get this error code:
Run Time error 1004
Method Range of object
Worksheet Failed

Any help here on why this would happen.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Greetings

VBA Code:
Sub Range_Name()
'Modified 10/17/2020 07:07:00 AM  EST 'Fahad
Dim sn As String
Dim ans As String
ans = ActiveCell.Address ' no need
sn = Worksheets(1).Name
Sheets(sn).ActiveCell.Copy Sheets(1).Range("B11") 'direct use
'Sheets(sn).Range(ans).Copy Sheets(1).Range("B11") ' if you need
End Sub
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim sn As String
Dim ans As String
ans = Target.Address
sn = ActiveSheet.Name
Sheets(sn).Range(ans).Copy Sheets(1).Range("B11")

End Sub
 
Upvote 0
The user wants to enter a Named Range into a cell and then have that range copied to a particular sheet. So the user enters Paul into range "B14" and then he wants to double click on "B14" and have the named range copied to a particular sheet. I'm vey familiar with Excel and like I said my Module script works exactly like I want but the double click script does not work.
 
Upvote 0
from My side double click script works, but has error
wrong.gif
 
Upvote 0
It wants to have the parent sheet for Range(ans) -- which defies the purpose of sn = Range(ans).Worksheet.Name
 
Upvote 0
It wants to have the parent sheet for Range(ans) -- which defies the purpose of sn = Range(ans).Worksheet.Name
So can you fix my double click script. I don't understand Parent sheet.
It works for a module script.

I need to know on what sheet is the Named range on.
I double click on "Alpha" which is a named Range in this workbook. I want to know the sheet name where the Named Range "Alpha" is.
 
Upvote 0
What is the scope of the named range? Workbook? -- If yes, then try
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Dim ans As String
    Dim nn As Name
    ans = Target.Value
    For Each nn In ThisWorkbook.Names
        If nn.Name = ans Then
            nn.RefersToRange.Copy Sheets(1).Range("B11")
            Exit Sub
        End If
    Next nn
End Sub
 
Last edited:
Upvote 0
What is the scope of the named range? Workbook? -- If yes, then try
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Dim ans As String
    Dim nn As Name
    ans = Target.Value
    For Each nn In ThisWorkbook.Names
        If nn.Name = ans Then
            nn.RefersToRange.Copy Sheets(1).Range("B11")
            Exit Sub
        End If
    Next nn
End Sub
Thanks for that script. That works just like I wanted.
I wonder why a module script works my way but not a double click script.
So thanks for that help
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top