Hyperlink: Problem with Spaces (Excel 2000)

[Archie]

I'm creating an index page for a workbook with a large number of sheets. I've got a formula which hyperlinks to any sheet, even if the name of the sheet is changed. For instance, clicking on the cell which contains the following formula will hyperlink to cell A1 of Sheet2: =HYPERLINK(CELL("address",Sheet2!A1)) [The workbook must be saved for this function to work]

Problem: The hyperlink stops working if either the target sheet name or saved file name includes one or more space characters. Can anyone see a way to modify the formula so that the hyperlink keeps working, irrespective of whether spaces are in the target sheet-name or target file-name? I've tinkered unsuccessfully with replacing spaces in the string with , but with no success.

Thanks in advance,
Archie

[LegareColeman]

Try:

<pre>=HYPERLINK(CELL("address",'Space Sheet2'!A1))
</pre>

[Archie]

I tried it, but I don't think your solution works. My formula automatically becomes your formula if "Sheet2" is renamed "Space Sheet2", but the space in the hyperlink string seems to cause the failure in the hyperlink.

Regards,
Archie

[LegareColeman]

OOPS! That looks like a bug in the HYPERLINK function. I was not able to find a way around the problem. However, I was able to use Insert Hyperlink from the Insert menu to create a link that would work. Can you use that?

[Archie]

I'm using this function for a document that will be used as a template by many different users, and I need the hyperlink to survive a renaming of a sheet by any user, so the Insert Hyperlink approach doesn't work for me.

Thanks,
Archie

[LegareColeman]

Sorry, then I don't have any other suggestions.

[reimer]

Legare,

Could a macro be written that would check Tab names and strip any spaces from the Tab name?

Chuck

[HansV]

That would be possible, but would users like it? Moreover, what if a user has "Sheet1" and "Sheet 1"?

[reimer]

Hans,

Agreed. It would may displease some of the users. The macro could be written to check against all tab names to avoid dupes, or replace spaces with a different character.
It was just a thought.

Chuck

[Wassim]

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> reimer

My Two cents worth <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

Your approch is very valid, but I would add that instead of looking at the worksheet name, which could be anything, the VBA code should look at the worksheet object name.

I think it is harder for a casual user to change the worksheet object name, well first they have to know where it is, and that makes it a better choice than the all visible worksheet name that you see in the worksheet tabs.

The VBA code should look at the worksheet object name, and compose the hyperlink for that worksheet and set the name of the worksheet from the worksheet tab for display.

Wassim

[Wassim]

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Archie

Please see my reply to reimer in this thread.

The VBA code should be looking at the Worksheet Object Name, in the VBE, vs the one in the tabs.

The former is harder to change by the user, and you can control it better via VBA code as well.

If a user adds a worksheet, this event will trigger the event handler and rename the object the way you want to.

Hope this helps.

Wassim

[HansV]

But Archie wants to create formulas with the HYPERLINK worksheet function. HYPERLINK does not accept the code name of a worksheet, it must work with the "visible" name of the worksheet.

[sdckapr]

You could try a macro approach instead of using hyperlinks.

On each sheet you need to "goto" with a link, create a named range onto the sheet using a descriptive name (rSheet1, rDataSheet, etc)

Then create a macro with the parameters of the range name of the sheet and the cell address:

<pre>Sub GotoCell(sRngName As String, sAddress As String)
Range(sRngName).Parent.Select
Range(sAddress).Select
End Sub</pre>


Where you would put a hyperlink, add an object (a FORMS toolbar label with no text is good) and assign a macro to it. the macro would be one line (eg):

<pre>Sub GotoSht1A100()
GotoCell "rSheet1", "A100"
End Sub</pre>


Of course you could have a macro decide based on some conditions in the workbook or a range, what the rangename and the address would be.

You could create multiple macros (instead of hyperlinks), or just a few that read info from the spreadsheet (whatever is required). The parent of named range will not be affected by the sheet name changing as the "refers to" will be updated. You could make the names "hidden" for an extra layer of protection from changing.

Steve