About Us

Philip J Smith

Thanks a lot. That worked a treat.

Regards

Phil

"Vergel Adriano" wrote:

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"

"Philip J Smith" wrote:

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

"Vergel Adriano" wrote:

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"

"Philip J Smith" wrote:

Hi

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

Regards

"Vergel Adriano" wrote:

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" wrote:

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 http://www.mvps.org/dmcritchie/excel...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
---------------------------

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Making a list of discrete numbers (names?) and occurences of each	partsman_ba	New Users to Excel	10	August 22nd 09 03:02 PM
List sheet names	StevenP	Excel Programming	13	July 31st 06 08:33 PM
how do i set up a list of names on a sheet frm various sheets in e	mcvities_69	Excel Discussion (Misc queries)	1	January 27th 06 02:51 AM
Making folders based on list of names	[email protected]	Excel Programming	1	December 6th 05 10:18 AM
Putting Sheet Names on a list	arcq	Excel Programming	3	March 9th 05 02:49 PM

Menu

About Us