Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to put together a sheet that has the capability to drill down into data. My thought on how to accomplish this was to use the Worksheet_BeforeDoubleClick function available at the worksheet level. My problem that I need some help on is I need to be able to identify the exact cell location that a user is double clicking on. I only want the drill down feature to work on a certain list of cells. All other cells I'll want excluded. If I can identify the exact cell that is double clicked on, I think I can make this work. For example, If a user clicks on cell A10, what VB coding can I use to help me identify the cell / target that was clicked?

    Thanks for any help or advice. Also, if you have any other suggestions that you think might be helpful with what I'm trying to accomplish. Those would also be appreciated. Thanks in advance!!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The doubleclick event has a "Target" argument, that is the item that has been clicked on. To avoid Excel doing the default action (go into cell edit mode) you must also cancel that action:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'What was clicked?
    MsgBox Target.Address
    'Cancel going into edit mode
    Cancel = True
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pieterse' post='771020' date='17-Apr-2009 09:52']The doubleclick event has a "Target" argument, that is the item that has been clicked on. To avoid Excel doing the default action (go into cell edit mode) you must also cancel that action:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'What was clicked?
    MsgBox Target.Address
    'Cancel going into edit mode
    Cancel = True
    End Sub[/quote]


    Thank you for this information. It is very helpful. I wasn't familiar with the .address argument. I'm seeing that the .address specifies the cell locations as for example $B$10. Is it possible to change this to the Row column format so that I can see it as (10,2).

    The reason for this is I have a metric Id that is in the column just to the left of a metric list (The metric list is where the user will click) and I want to refer to that Id so I can perform a lookup to make the drill down feature work. So If I click in cell ($B$10) or (10,2), I want to find the metric ID which would be in cell ($A$10) or (10,1).

    I was just thinking it would be besier to subtract 1 from the column number to find the metric ID than if I was to try and find the coluimn letter.

    Thanks again for your help with this.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use Target.AddressR1C1 to get the address in R1C1 notation, for example "R7C4" for cell D7.
    Or you can inspect the row number and column number separately: Target.Row is the row number and Target.Column is the column number.

  5. #5
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Only proceed if correct column is clicked
    If Not Target.Column = 2 Then Exit Sub
    Cancel = True
    'Return Clicked and adjoining values
    MsgBox Target.Value & vbCr & Target.Offset(, -1)
    End Sub

  6. #6
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks everybody. I appreciate the help with this topic.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Target is a Range object (a set of cells) and there are lots of ways to work with that object.

    e.g. if you need a cell to the immediate left:

    MsgBox Target.Offset(0, -1).Value

    Or just below:

    MsgBox Target.Offset(1, 0).Value
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •