Carry the Value of a Hyperlink Cell to a location

  • Thread starter Thread starter DoctorV
  • Start date Start date
D

DoctorV

We have a main worksheet which is a table that lists 2 letter stat
codes as hyperlinks in one of 3 columns and then links out to a ne
sheet. i was wondering if there was any way in some event code that i
you click the state code for CO for example it would take you to th
sheet like it is doing , BUT IN ADDITION carry the value CO to
specific location say Sheet2!$A$2 so it could be part of an Index(Matc
Statement I have set up
 
Currently the Hyperlink takes you to cell A1 of the rating-non-sf
worksheet. What I wanted to do in addition to that when CO was clicke
was to take the value CO to Sheet2!$A$
 
Could you use a doubleclick or rightclick to start the macro?

(You could use selection of the cell, too, but you better be sure that you
select just the cell you want or off you'll go!)

I set up a worksheet (sheet1) with state codes in A1:A5 (just a sample).

I created 5 worksheets named as those state codes.

Right click on the sheet1 tab and select view code, paste this in:

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

Dim wks As Worksheet

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a5")) Is Nothing Then Exit Sub

With Target
Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(.Value)
On Error GoTo 0
If wks Is Nothing Then
MsgBox "The " & .Value & " Sheet wasn't found!"
Exit Sub
End If

Worksheets("sheet2").Range("a2").Value = .Value

Application.Goto wks.Range("a1"), scroll:=True
End With

End Sub

And a couple of links that may help:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
and
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top