2

Hoping someone can help with a VBA problem I am having.

I want to change the value for a Named Range when a user double clicks on the cell. There are two worksheets, the initial worksheet with a value in a cell and then the destination worksheet which contains the Named Range Account_Number.

This is my code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    [Account_Number] = ActiveCell.Value 'assign the value to the Named Range

    Range([Account_Number]).Select ' go to the Named Range on the other worksheet

   'Sheets("Transaction Listing_LINKS").Select 'the workaround to the problem below
End Sub

What I expect is that the Named Range to be populated and selected.

The problem is I get a

"1004 error: Method 'Range' of object '_Worksheet' failed"

error when I double click on the cell.

The strange thing is that if I only use one worksheet it works without any problems.

Can anyone explain to me why this would happen and how to avoid it?

The only fix/workaround I can think of is to code the selection of the worksheet to force it to select it.

Thank you

Community
  • 1
  • 1
veganWorld
  • 45
  • 1
  • 2
  • 4

1 Answers1

2

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'assign the value to the Named Range
  Sheet2.Range("Account_Number").Value = ActiveCell.Value
  ' go to the Named Range on the other worksheet
  Application.GoTo Reference:="Account_Number" 
 'Sheets("Transaction Listing_LINKS").Select 'the workaround to the problem below
End Sub

Of course you'd need to change "Sheet2" to the destination worksheet name containing the named range.

JimmyPena
  • 8,694
  • 6
  • 43
  • 64
Mark M
  • 976
  • 5
  • 14
  • + 1 for using `Application.GoTo` instead of `.Select` ;) – Siddharth Rout Jun 10 '12 at 03:53
  • Thanks for that, got it to work without needing to reference the worksheet. Any idea why it works for a single worksheet and not when two worksheets are involved? – veganWorld Jun 10 '12 at 04:09
  • As a novice can you please explain .Goto is perfered to .Select? – veganWorld Jun 10 '12 at 04:17
  • Select is clunkier and less semantic (for this purpose). It is clunkier because you need to select the sheet, then select the range, instead of doing it all in one step. In other words, if you execute `Sheet2.Range("A2").Select` and Sheet2 is not active you get an error. It is more semantic because it clearly expresses your intent of navigating the user to the cell, whereas Select can be used for other purposes. – Mark M Jun 10 '12 at 14:15
  • This is good, but you should really explain WHY `.Select` didn't work. If I am not mistaken, `.Select` only works on the active sheet. That is why the error occurred, and why it works if there is only one worksheet. For reference: [VBA Excel Select vs Activate](http://stackoverflow.com/a/7188721/190829) – JimmyPena Jun 10 '12 at 15:11
  • @JP: Thanks. I thought that was made clear when I explained that in order to choose the range on Sheet2 to you had to select that sheet first, and that if you tried to use Sheet2.Range("A2").Select without Sheet2 being active you would get an error. – Mark M Jun 10 '12 at 15:14