Making Hyperlinks from a list of sheet names

[Philip J Smith]

Hi.

Could some-one point out the error in the following code please?

I have used the code below to convert sheet names listed as lables into
hyperlinks, modifications noted in text. As the code was designed to convert
file links to hyperlinks rather than Worksheet names I have attempted to
modify it.

When run the code formats the lables as hyperlinks, but when I try to follow
the link the following error message appears.

"Reference is not Valid"

The worksheets are in the same workbook as their list. I have hardcoded the
workbook name, dangerous I know but I will sort that out once I get the code
working.

Regards

Phil
----------------------
Sub MakeHyperlinks_B()
'Copied from www.mvps.org/dmcritchie/excel/buildtoc.htm#MakeHyperLinks
'on 15 March 2007.
'Modified so that the range is in column B rather than D
'SubAddress Added to ActiveSheet.Hyperlinks
Dim cell As Range, Rng As Range
Set Rng = Range("B2:B" & Cells.Rows.Count). _
SpecialCells(xlConstants, xlTextValues)
If Rng Is Nothing Then
MsgBox "nothing in range"
Exit Sub
End If
For Each cell In Rng
If Trim(cell.Value) <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:="SummaryBilledByMonth.xls", _
SubAddress:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End If
Next cell
End Sub
---------------------------

 

[Don Guillett]

Why bother. Just put this code in the sheet module where the list is and
double click on the typed name such as

sheet7

to goto shee7

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Sheets(ActiveCell.Value).Select
ActiveSheet.Range("a4").Select
End If
Application.DisplayAlerts = True
End Sub

 

[Vergel Adriano]

Philip,

You don't need to pass a value for the SubAddress parameter. It is used for
hyperlinking to an address in the same document. For hyperlinking to another
document or web url, you pass the Address parameter. I believe you also will
need to pass the full path of the target workbook. ie, assuming it's in root
of Drive D, you would code it like this:

ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:="D:\SummaryBilledByMonth.xls", _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value

 

[Philip J Smith]

Hi

Thanks for your response but, I am linking to a worksheet in the same
workbook (Document).

Regards

 

[Philip J Smith]

Hi Don

Thanks for your response. I’ve followed your instructions, but the code
either doesn’t work or falls over on the call for GetWorkbook. I think that
I might be missing something.

Since I don’t have that code for GetWorkbook why do I need it the call?

I think that the final line of code puts the cursor in Cell A4 of the
activated sheet, I think that I can change this to any other cell, is that
true?
Regards

 

[Vergel Adriano]

Sorry, I didn't get that. So, the target sheet and cell address are in column
B? For hyperlinks to a location in the same workbook, you need to pass a
value only for the Subaddress parameter. The Address parameter, you need to
set pass as "". So, try it this way:

ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:="", _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value

As another example, the code below will create a hyperlink to Sheet2!A1 of
the current workbook. The link will be in cell A1 of the Activesheet.

ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="", _
SubAddress:= "Sheet2!A1", TextToDisplay:="Click Me"

 

[Vergel Adriano]

Correction, I meant to say to try it this way:

ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value

 

[Philip J Smith]

Hi.

Thanks for coming back. The second row of your alternative example seems to
hold the answer

SubAddress:= "Sheet2!A1", TextToDisplay:="Click Me"

Can you please tell me how do I make "Sheet2!A1" variable? Sheet2 is the
worksheet name in the cell. The worksheet name has spaces. eg "Billed Feb
07".
I can't hard code the references as each month a new worksheet will be added
and the list of worksheets updated.

I can't work out the code to concatenate the worksheet name and the cell
reference and pass the correct reference to the hyperlink function.

I've tried the following,

SubAddress:= concatenate(cell.value,"!","A1")

but it doesn't seem to work

I even tried

SubAddress:= concatenate("'",cell.value,"!","A1","'")

There obviously something wrong with the sytax, but it's beyond me.

Your continued help would be appreciated.

Regards

Phil

 

[Dave Peterson]

Maybe...

...., subaddress:="'" & cell.value & "'!a1", ...

 

[Vergel Adriano]

You were very close. You enclosed the whole thing in single quotes, but only
the sheet name need to be enclosed. So,

SubAddress:= "'" & cell.value & "'!" & "A1"

 

[Don Guillett]

Ok. You may have to delete or comment out the getworkbook part unless you
want me to post it.
Other than that, if you have

Sheet7

typed into a cell and double click on the cell you will goto sheet7.cell a4
(change as desired). And, as I said originally, this has to be in the SHEET
code of the worksheet where the name is typed in the cell.

if you have my summary typed into the cell>double click on that to go there.


This also works
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
' GetWorkbook ' calls another macro to do that
' Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

 

[Don Guillett]

UN comment the

Else

 

[Philip J Smith]

Thanks a lot. That worked a treat.

Regards

Phil