Module Script works. Double click post does not work

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

from My side double click script works, but has error
wrong.gif
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,636

ADVERTISEMENT

It wants to have the parent sheet for Range(ans) -- which defies the purpose of sn = Range(ans).Worksheet.Name
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,636
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:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,010
Messages
5,599,330
Members
414,305
Latest member
scarletX

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