1

I'm trying to add DoubleClick event for a range of cells starting from B:27 to B:x(any number of rows). So I want to restric to only column 'B' starting from row # 27

When any cell in the defined range is clicked I'll update the Target.Value in another cell and call a sub routine.

The current code looks like this. This is from a previous developer and it works in one of the sheet but now I'm creating a similar report on another sheet and it gives me the error

Method 'Range' of object '_Worksheet' failed.

I'm not sure where "DblClikRange" range is defined, I looked in Module code as well I searched for the word throughout tha VBA code I could not find it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
   If Not Intersect(Target, Range("DblClikRange")) Is Nothing Then
     On Error Resume Next

     Range("B22") = Target.Value

    ' call the sub to load data
      Call MyDemo
   End If
End Sub

Any idea where to look up for this named range variable or how to do this in a different way? Any help would be appreciated.

Dennis R
  • 3,195
  • 1
  • 19
  • 24
  • 3
    `Any idea where to look up for this named range..` yes, read [this](http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010147120.aspx). Also might be helpful: [Why I should use On Error Resume Next judiciously](http://stackoverflow.com/questions/21673529/if-not-function-proceeds-when-value-is-the-one-specified/21673617#21673617) – Dmitry Pavliv Jun 06 '14 at 18:30
  • 1
    There should be a drop-down box in the upper left hand corner of the workbook (right below the ribbon). Click on it and you should see the range you are referring to. Highlight it and excel will take you to where it is. – Matt Cremeens Jun 06 '14 at 18:34
  • Thanks @simoco I'll go through the links you provided – Dennis R Jun 06 '14 at 18:40
  • @MattCremeens yes, I could see the dropdown with the named range variable. – Dennis R Jun 06 '14 at 18:43
  • +1 to simoco for the comment about On Error Resume Next. Seems like every code I see posted on here has that in it. – Dave.Gugg Jun 06 '14 at 19:17
  • +1 to simoco and Matt for the direction on how to locate the named range variable and how to define your own. I defined my own variable now based on the instructions given in the link and now works the way I wanted. – Dennis R Jun 06 '14 at 19:25

1 Answers1

0

You don't need to used a named range if you don't want to, you can just explicitly define the cells in the code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
         If Target.Row => 27 Then
             Range("B22") = Target.Value
             ' call the sub to load data
             Call MyDemo
         End If
    End If
End Sub

It really just depends on what you are most comfortable with - it seems like you aren't used to working with named ranges, so just use the code above.

Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43
  • Good to know the alternate approach. It works too. I don't really need to use the named ranges here because I just reference the cell ranges at one place. – Dennis R Jun 06 '14 at 19:33