hello and welcome to this video tutorial from computergaga.com and in this
video we are going to learn how to hyperlink to a hidden worksheet so
imagine this scenario I have this main sheet at the bottom here and I've also
got a sheet for each fruit of apples oranges pears and pineapples and on this
main sheet I have a kind of table of contents if you will which links to each
of those other sheets and at the moment that's going to work fine because I've
already taken the effort of selecting these cells doing the ctrl K keyboard
shortcut and setting up a hyperlink to the other sheet so they're going to work
absolutely fine if I click on pears it takes me two pairs if I click on apples
it takes me two apples and I've also got a link on each of those sheets in cell
a1 that just takes me back to Maine and that's going to work fine too but what
if one of those sheets was hidden if I was to right mouse click and hide the
pears sheet and then go and try the hyperlink on Main then it's not going
to work and we need it too so this is going to take a little bit of VBA and
we're going to get that working so let me click on my Developer tab at the top
and the ribbon and click visual basic to get into the visual basic editor and
we're not going to need a module for this we're going to need to write some
code in the worksheets themself because we're going to run it off an event this
macro is going to run off an event so I'm going to double click on the sheet
one window here now I actually don't need to at the moment because I actually
already there I'm going to see at the top and the title by it's a sheet one
code and sheet one is the main sheet in this case but you would simply double
click that if that's not the case that would open up the appropriate code
window in the first drop-down list I would
choose worksheet and in the second drop-down list I'm going to choose a
different event or procedure so it defaults to the selection change events
when dealing with worksheets but the one that I want is going to be the follow
hyperlink event so if somebody clicks a hyperlink on that sheet so
that's one name of the event follow hyperlink is a little bit misleading it
just means if I clicked on it it's not actually going to perform the follow
which you'll see very shortly so that's going to put that sub on here so blog
ends change the name of this it's essential for it to work we'll leave
that well alone but we do have this up we've now got to write the code that's
going to actually unhide the kind of destination of the hyperlink sheet and
then actually move ourselves to it so there's two bits of information that we
need to know we need to know which link was clicked and then where is that link
going so that we're able to unhide the right sheet and take us hours to it now
the first bit of information is already given to us they're telling us that
target this parameter this argument is the hyperlink that was clicked so we can
just refer to as target that's been given to us but we need to know where is
that link going because otherwise we're not going to unhide the right sheet now
I'm going to demonstrate two different ways of doing this now just before I
start this if I go back to the worksheet one thing I'd like to point out is the
names of the links like apples oranges pears are the same as the name of the
worksheet so my first technique is just to say look wherever the hyperlinks
called the sheet see exactly the same name just go to that sheet and then I'm
going to show a second of example of being able to ascertain the address of
the link because for you guys the sheet might not be the same name as the
clickable kind of next so I'm going to shoot two different
ways at one thing I am going to stop with is I'm going to declare a string
variable now if you're quite new to VBI I'll have a link in the description of
this video to my online VBA course so you've become a badass in VBA pretty
quick but for those of you however a little bit more experienced this should
be quite commonplace what I'm doing at the moment I'm declaring a variable
called Sheets name shtname and it's string because it's gonna handle text ie
carrying the name of the sheet and then going to assign it to it and I'm going
to assign target the link is clicked I'm gonna put in my period this . it will
list the different properties of methods available to me and I want to start with
one good name so the name of the link which as I mentioned a few moments ago
is the same as the name of the sheet so for me in this example that would work
very well but I'm going to come back to that with another example in a moment
next up though now that we've got that name in that variable I want to make it
visible so I'm going to put sheets sht name today is my variable coming in handy
what sheet I would the one that I just found out matches the name of the link
that was clicked then I'm going to put in my full stop visible equals excel
sheet visible so I get to make that sheet visible okay
once it's visible we need to go to it so sheets I could just copy and paste from
above but I'm underway now select keep things simple for now now it is visible
select that sheet ok let's test that this works before we go any further then
we come out of the editor here and remember the pair's sheets at the bottom
is not visible so let me clear compares and look at that it unhides the sheet
and takes us to it so that is working let me right mouse click and hide a
different sheet let me hide the oranges sheet this time go back to main and
let's go back into the editor and look at another way that we can get this
working let me right mouse click and hide a different sheet this time I'll
hide the apples sheet go back to main and back into visual basic so we can see
a different way of unhide in a sheet from a hyperlink because the name of the
link in the name of the sheet might not match so what I'm going to do this time
is comment out the previous approach and start a new one so we're going to assign
to sht name the target dot sub address so it's important here that I'm using
sub address and not address address is when you're linking to a separate
workbook or to a different web page but here I'm linking to a different sheet
within the same workbook which is a sub address now if you think about when
you're writing formulas across different sheets to sales on different sheets
think about its structure you always have the name of the sheet followed by
an exclamation mark followed by that cell reference so what I need to do is
the address or a sub address that this link is going to I want all of
the characters up to the exclamation mark because that is the name of the
sheet yeah I can't rely on a name of the link pin the same as the sheet but the
sheet name is in that address it's the thing up to the exclamation mark so I'm
going to use that but I need the left function because I want all the
characters from that sub address up to the exclamation mark so the left
function just like you get in Excel normal we have a VBA version the string
that were taken from is the sub address but the length is the problematic bit I
don't know how many characters it's up to the exclamation mark so I'm going to
use in string i n STR this is a function that will look for a string within a
string so techs are in some text and return it's starting position
now the starting position of the actual search here this is from the first
character comma string one is the string that you're looking within and in this
example that's target or sub address so let me copy and paste that another comma
these mounts are the string that I'm searching far we've in sub address and
that isn't its Cremation mark from here I can close off that in string function
and put minus 1 because we're looking for the length here for left to take and
the last character of the sheets name is 1 before the exclamation mark another
close bracket for the left function and we're done there I can come back out of
this and let's see if this is working so let me minimize this I remember it's the
apples sheet that I hid see if I click on my apples hyperlink it works it has
taken me to that sheet even though it was hidden and if our hyped the
pineapple sheet back to main click on pineapples
he works so that example was for evens the sheet has a different name it will
still be able to work whereas my previous technique was ensuring that the
sheets and the links are called the same thing for a slightly easier life and
easier approach now to take this one stage further it might be that once your
link from Maine has unhidden and followed a link to that sheet I have
this back to Maine link and if I click on that it will work absolutely fine but
what you might want is the sheet to hide itself again yeah was hidden it went to
it you click a link to go back you know fantastic but it was hidden to start if
you probably want it hidden again there's different techniques for that as
well I could use another follow hyperlink event in order to hide it and
that wouldn't be too tricky but there may be other hyperlinks on this sheet or
maybe instead of following a hyperlink maybe somebody just clicks from pine
apples to oranges or some yeah maybe other sheets we have here in this
situation if it was more than just these so they may not actually follow the
hyperlink so what I'm going to do instead is back into Visual Basic and
I'll pick on the apples sheet here so if I just double click on apples and the
project Explorer on the left here to open up a code window for that and then
from the two drop downs at the top I'll choose worksheets from the first one and
from the second one I'm going for deactivate so instead of follow
hyperlink I'm going for deactivate so as soon as somebody leaves that sheet
whether that's via a higher plane chorus by clicking somewhere I don't care
they've just left that sheet so I can delete that and come inside here and a
nice simple little bit of code me visible I don't have to reference the
sheet in the way I did them a few moments ago I was capturing that
variable and sh t dot name I'm just referring to myself in this case so me
divisible equals excel sheet hidden so before we do invisible now we're hiding
it now that is for the apples sheet and I might just leave there's apples but
really we would copy and paste that onto the other sheets oranges pears
pineapples I'm going to leave that for a moment something clicking around don't
get too crazy I've only got four sheets here so copying and pasting it wouldn't
be like a huge step if you have a few more may take a little bit more time but
nothing too crazy and if you're doing this before we even get to this point
you can always copy the sheets so remember I did that to the apples sheet
so let me hide apples go to the main sheet click the link which successfully
unhides it and takes me back button if I click on back to main to go back to main
apples automatically hides itself so that little bit of code just add that
little bit more so we can get a link to go to a hidden sheet but then we can
also get the sheet to hide itself when you exit that sheet whether it be via
hyperlink or by some other method and we would copy that to the other sheets in
this case all those of your choosing I hope you found this video useful please
check out some of our other video tutorials on our YouTube channel and
come check us out at computergaga.com
you