PDA

View Full Version : EXPERT CHALLENGE: HYPERLINK NOT WORKING?



HI005P
12-20-2007, 08:23 AM
The attached XLS uses a VBA module "SEARCH" (DoFindAll) that allows you to find a key word in Excel. The search results are returned to a new worksheet named "FindWord". The results are listed with hyperlinks pointing back to the cell(s) the text appears in. The search process works fine except for the hyperlinks. The search results are shown in blue and underlined as if they have been assigned a hyperlink, but when you click on the link an error pops up with "Reference is not valid." Can you suggest what adjustments are needed in order for the hyperlinks to work correctly .

Thank you very much for your assistance.

ps

See attached file SearchWord_Test on reply below.

HI005P
12-20-2007, 08:28 AM
Please see attached file.

Andy Pope
12-20-2007, 01:20 PM
The problem is the spaces in your sheet names. You need to enclose the name in single quotes.


ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & Counter + 2), _
Address:="", SubAddress:=FindSheet(Counter) & "!" & FindCell(Counter), _
TextToDisplay:="'" & FindSheet(Counter) & "'!" & FindCell(Counter)
Range("B" & Counter + 2).Value = FindText(Counter)

HI005P
12-20-2007, 02:18 PM
Andy,

Thank you. Problem solved. The solution wasn't single quotes on the worksheet name under TestToDisplay:=, but under SubAddress:=. However, I suspect you already knew that but wanted me to discover it.

Brilliant!!!

Here's the fix...

ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & Counter + 2), _
Address:="", SubAddress:="'" & FindSheet(Counter) & "'!" & FindCell(Counter), _
TextToDisplay:=FindSheet(Counter) & "!" & FindCell(Counter)
Range("B" & Counter + 2).Value = FindText(Counter)

Andy Pope
12-20-2007, 02:22 PM
:doh: sorry about that. But glad you managed to fix my typo.

HI005P
12-20-2007, 02:28 PM
you da man...

Thanks again.