Results 1 to 11 of 11
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Goto source cell (2002 SP-2)

    I don't know if this is even possible, but it sure would be nice if it is. In the attached example I have a cell (E5) that returns the date that the MAX value (F5) for column "D" occurs. Might it be possible to "click" on either the date or the value (E5 or F5) and have it "goto" the row where the value exists? Both the date and the value of E5 and F5 change as new data is entered, so the "link" would have to be dynamic. I have looked in help under "link", "hyperlink" and "goto" but they don't seem to address the issue, or they're just over my head (more likely). Any and all assistance greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Goto source cell (2002 SP-2)

    Enter a "label" in Cell E5 (format it to be invisible: no lines, no text, etc. Assign this macro to it:
    <pre>Sub GotoDate()
    Dim vValue
    Dim lrow As Long
    With ActiveSheet
    vValue = .Shapes(Application.Caller).TopLeftCell.Value2
    lrow = Application.WorksheetFunction.Match(vValue, .Range("A:A"), 0)
    .Cells(lrow, 1).Select
    End With
    End Sub</pre>


    The macro will look for the value that comes from the cell that the label is in (4/12/04) and then do a Match in Col 1 to get the row that has that date. It will then select that cell.

    Steve

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Goto source cell (2002 SP-2)

    Steve:

    I kind of thought I was in over my head - now I'm sure of it. I'm brand new to Excel so I haven't a clue how to enter a "label" in the cell, or even how to assign a macro to it. I do recognize the VBA though (only because I have worked in Access). I do appreciate your assist, but unless you're up to doing some hand-holding I'll have to abandon ship. BTW, I did manage to check "accept labels in formulas" if that even applies to what you are saying. Dazed and confused.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Goto source cell (2002 SP-2)

    Add the code to a module:
    Open VB (alt-F11)
    insert module
    open module 1
    copy the code to the module "code pane" (on the right side)
    close VB (ctrl-q)

    Now add a label
    Make sure the FORMs toolbar is visible (view - toolbars - check "Forms" if it is not checked)
    On the forms tool bar, you will see a button that has an Aa on it. This is the label

    Press it, and the cursor will turn into a "+" Now "draw a rectangle within cell E5. (it is import that the topleft corner is within the cell)
    When that is complete select the text "Label 1" and delete it.
    right click on the border, select "Assign macro" and choose the macro I gave you

    When you move the mouse over the cell it should be a hand with a finger indicating that you can press it.

    Hope this helps,
    Steve

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Goto source cell (2002 SP-2)

    Steve:

    Thanks for your patience! I have managed to make some progress, but am getting the following error:
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Goto source cell (2002 SP-2)

    Change the :
    Sub Label3_Click()
    GotoDate
    Dim vValue

    To either:
    Sub Label3_Click()
    Dim vValue

    or
    Sub GotoDate()
    Dim vValue

    Steve

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Goto source cell (2002 SP-2)

    Steve:

    Thanks for your help. It works great!
    <pre>Sub GotoDate()
    Dim vValue
    Dim lrow As Long

    With ActiveSheet
    vValue = .Shapes(Application.Caller).TopLeftCell.Value2

    lrow = Application.WorksheetFunction.Match(vValue, .Range("A:A"), 0)
    .Cells(lrow, 1).Select

    End With
    End Sub</pre>

    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Goto source cell (2002 SP-2)

    Th advantage of using the "GotoDate" name is that you could add a label to another cell that yields the date (the min perhaps, or some other date) and assign it to the same macro and it will go to the date in that other cell. Whatever cell you add a label to, it will goto the MATCH in column A of that sheet: all with the same macro since the macro reads the value from the cell that holds the shape which calls the macro.

    Steve

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Goto source cell (2002 SP-2)

    Steve:

    What a great solution! I've tried it in E3 as well and it works perfectly (and without having to re-write the code). It does not however work in A3 or A4 (for obvious reasons) but I've already worked around that issue. Thanks again for the solution and the lesson! A pleasure to meet you.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Goto source cell (2002 SP-2)

    As an alternative to Steve's solution, you could employ the Worksheet_BeforeDoubleClick event.

    The following code should select the source (first matching) cell for A3, A4, E3 or E5 in the range A6:A65536.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Union([A3:A4], [E3], [E5])) Is Nothing Then
    Range(Evaluate("ADDRESS(5 + MATCH(" & Target.Address & ",A6:A65536,0),1)")).Select
    End If
    On Error GoTo 0
    End Sub

    Note that this code does not go in a general module. To enter it, copy the code, then right click on the relevant sheet tab and paste the code into the code window.

    A double click on any of the relevant cells should then take you the cell with the matching value. I have included some error trapping to cater for the possibility that a match may not be found.

    Andrew C

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Goto source cell (2002 SP-2)

    Andrew:

    Thank you for your input. It's nice to know that kind and generous contributors are not just restricted to the Access board <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    I do appreciate your advice and can see where tab specific functionality would be a real asset; rest assured your suggestion has been save to my new Excel CD. Again, thank you for your help and the opportunity to work another "room" of the lounge; no better people anywhere!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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