Create a HYPERLINK with a macro?

  • Thread starter Thread starter bobgerman
  • Start date Start date
B

bobgerman

Can anyone tell me how to create a hyperlink to a different sheet with a
macro? The sheet will always be the third tab when the macro runs but
may change locate subsequently so the hyperlink must refer to the sheet
name.

I appreciate your help.
 
This formula should do what you want.
=HYPERLINK("[Yourworksheet.xls]yourworksheet!a1")
But, I prefer to use a double click event macro to do this
right click sheet tab>view code>insert this
Then just double click on the cell with a typed name of the sheet desired.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim mysheet As String
mysheet = Trim(ActiveCell.Value)
On Error GoTo no
Sheets(mysheet).Select
Exit Sub
no: MsgBox "No Sheet"
End Sub
 
Don,

Once again, thanks for your help. You are right about using the "doubl
click" method. It works wonderfully.

However, I failed to mention that this spreadsheet is ultimately bein
saved as an "Interactive" HTML and published on a web site. I can'
seem to get the "double click" method to function in the HTML page.

I think I really need to use the hyperlink but, being the nucklehea
that I am in programming, I'm not sure how to implement the formula yo
provided:

************************************************

=HYPERLINK("[Yourworksheet.xls]yourworksheet!a1")

************************************************


The 'recorded' macro (when creating the hyperlink manually) looks lik
this:

************************************************

Sub Hyperlink()
'
' Hyperlink Macro
' Create hyperlink in worksheet 'Index'!A2 to worksheet named same a
data found in 'Index'!A2
'

'
Sheets("Index").Select
Range("A2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=""
SubAddress:= _
"'123456'!A1"
End Sub

************************************************

The name of the sheet being hyperlinked to (currently named '123456'
is also the value in the cell I am tring to create the hyperlink in
'Index'!A2.

Each time the macro will run the new hyperlink will point to
different sheet. (A new record will have been inserted into row 2:2
pushing all previous records down, prior to running the "Hyperlink
macro.) The new hyperlink is ALWAYS created in 'Index'!A2
 
Sheets("Index").Select
Range("A5").Select
Dim mycel As String
mycel = (ActiveCell.Value)

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
mycel & "!A1:B1"



Using info originally obtained from Don...Thanks Don!
 
Back
Top