Module Script works. Double click post does not work

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
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

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

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
477
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,247
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

Active Member
Joined
Mar 9, 2009
Messages
477
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,605

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,247
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,605
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,247
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,118,134
Messages
5,570,348
Members
412,320
Latest member
sixnine0312
Top