Click on a hyperlink to hide or show a spreadsheet

[Squeaky]

Hi,

I have a workbook that can contain anywhere from 5-20 worksheets. On the
first page (INDEX) I have a macro that will display all the names of the
spreadsheets. I have formulas that divide the speadsheets between the hidden
and the unhidden spreadsheets and places them in their own columns. Using the
hyperlink function each spreadsheet name then displays as a hyperlink, and
when I click on it, it will take me to that spreadsheet. What I want is to be
able to click the hyperlink to a hidden speadsheet, have it unhide, and go to
it. It must be dynamic because the spreadsheet names can change or vary, and
the amount of spreadsheets vary. The INDEX page is always first, and it
contains a REFRESH macro that runs whenever new pages are added.

 

[Bob Bridges]

This one's got me puzzled. Nothing I think seems to work.

You could put the name of the sheet in a cell without a
hyperlink; the user could then click on the cell to activate
a macro that would detect the cell that was clicked, get
the contents of that sheet, unhide the sheet and jump to
it. The only problem is that I can't find a Click event for
a cell, so that doesn't work. (Besides, how would you
hide the sheet again afterward?)

You could put a command button in the cells that
represent a hidden sheet, and connect code to each
button to do what you want. But how do you make
the button's contents or caption fully dynamic?

Is there a way to make the URL point to the execution
of a VBA module rather than to an internal sheet? I
see that happening in MSIE; maybe there's a way to
do it in VBA/Excel, too.

 

[Squeaky]

Hi Bob,

I can insert a command button to run a Macro if I need to. I have other ways
around that, thats not a problem. I can't seem to get the VBA to recognize
the name of the spreasheet that is on the hyperlink. The cells that contain
the hyperlinks will not change location, but the name of the spreadsheet in
the hyperlink and the order might.

 

[Bob Bridges]

I can't tell exactly what you're saying. Let's spell it out:

1) Can you, without using VBA, create a hyperlink from a
cell in one worksheet that when clicked will switch over
to display another worksheet?

2) Can you write VBA code that, when executed, will
change the active sheet from one to another?

3) You say you can't get VBA to recognize the
worksheet name that is in the hyperlink. I think
you mean you have a hyperlink set up, of the
type I mention in question 1, and you also have
some VBA code trying to read it. a) What
advantage do you get from combining both?
And b) I've never tried that, but from the
documentation I see how the various objects
should interact; what error are you getting?

4) You say "The cells that contain the hyperlinks
will not change location". a) By "location" you
mean the URL each link is pointing to, right? b)
Do you mean you try to change the destination
address of a link and cannot? Why not?

All of this is an attempt to get you to say more
clearly what's going wrong. Spell it out, please.
Like this: "When I select A5 and hit <Ctl-K>,
I get a window to change the URL, but when I
then click on..." And so on.

 

[Squeaky]

Hi Bob,

Actually I worked it out. I realized I did not need the hidden binders to
have an actual hyperlink so I eliminated that. In case you are wondering, I
answered your questions in-between.

I can't tell exactly what you're saying. Let's spell it out:

1) Can you, without using VBA, create a hyperlink from a
cell in one worksheet that when clicked will switch over
to display another worksheet?

Yes. I use a VBA code to list all of the Tab names on a spreadsheet, then
use hyperlink code so when I select the spreadsheet name, that spreadsheet
becomes active. Works fine.

2) Can you write VBA code that, when executed, will
change the active sheet from one to another?
Sure.

3) You say you can't get VBA to recognize the
worksheet name that is in the hyperlink. I think
you mean you have a hyperlink set up, of the
type I mention in question 1, and you also have
some VBA code trying to read it. a) What
advantage do you get from combining both?
And b) I've never tried that, but from the
documentation I see how the various objects
should interact; what error are you getting?

Since the names of the spreadsheets can change names and locations, I was
using a hyperlink formula with an Indirect command. I think that was
confusing VBA.
Once I removed that from the hidden folders it started working.

4) You say "The cells that contain the hyperlinks
will not change location". a) By "location" you
mean the URL each link is pointing to, right? b)
Do you mean you try to change the destination
address of a link and cannot? Why not?

I meant that cell c3 will contain the name of the first hidden document,
cell c4 will contain the second, etc. The names of the links will change as
the spreadsheet names do, but c3 will always contain the name of the first
hidden document.
No to the last question.