Shana has a dashboard report with 50 worksheets. She has a menu with hyperlinks to the 50 different worksheets. Here's the catch - she wants to keep the 50 worksheets hidden until someone clicks on the hyperlink.
Today's podcast covers hyperlinks, the FollowHyperlink event handler, an introduction to the Watch window while the macro is in break mode, and a counfounding problem where Excel VBA seemingly could not handle Range("A1").Select.
Today's podcast covers hyperlinks, the FollowHyperlink event handler, an introduction to the Watch window while the macro is in break mode, and a counfounding problem where Excel VBA seemingly could not handle Range("A1").Select.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel for MrExcel Podcast, Episode 1729: Hyperlink to a Hidden Sheet.
Hey, welcome back to the MrExcel netcast.
Today's question's sent in by Shanna.
Shanna has a Dashboard Report-- she has a menu with 50 Hyperlinks, and then 50 Worksheets back here, and wants be able to link from the Hyperlink to the Worksheet.
But, here's the thing: The 50 sheets are hidden.
How can you Hyperlink to a hidden sheet?
Well, first, let's set up a Hyperlink here-- so, I'm going to do Ctrl+K.
Now, we're going to link to a place in this document.
It's going to be Part 5, Cell A1.
Click OK, and then on each of the sheets, I imagine there's a Hyperlink that goes back-- it goes back-- to the menu, Cell A1, so that we can jump to a sheet, jump back.
Alright, but right now everything is visible; Shanna has everything hidden.
So, Home, Format, Hide & Unhide, Hide Sheet-- those are all hidden and, now, these Hyperlinks are not going to work, because there's nothing to go to, right?
Very, very frustrating.
Well, what we're going to have to do is, we're going to have to use a little bit of VBA (Alt + F11) in order to un-hide the sheet, so when they click on the Hyperlink we're going to un-hide the sheet, and then, you know, let the Hyperlink work.
So, here's what you do.
You go to Alt+F11.
If you don't see the Project Explorer-- View, Project Explorer, or Ctrl+R. And we want to go to Sheet1, we're going to double-click Sheet1 to get to the code pane for that sheet.
This isn't a regular Macro that's written on a module like this one down here, it's written on the actual sheet's code pane.
Top left Drop-down, there's only one thing to choose, that's Worksheet.
And then, they always choose a SelectionChange by default.
We'll get rid of that-- go to the top right hand Drop-down and there is an event for FollowHyperlink.
FollowHyperlink, okay.
Now, for those of you that are kind of new to VBA, or even those of you who have been around VBA for a while, you know, sometimes you don't remember all of the properties that are available for a Hyperlink.
So, right here they've given us a variable called Target As Hyperlink, and I can't remember what, exactly, I need.
So, x = Target.
alright, and they're offering me Address.
Well, maybe address is what I want-- x = Target.Adress-- but I want to see-- I want to see-- what happens.
So, debug.Print x , alright?
Just to see what's there.
And then, the most important part, I'm going to Stop, so that way the Macro goes into Debug Mode, while this Variable is still valid-- still, you know, alive.
So, now, we'll come back here, we'll click, and that actually-- see that-- runs that Macro, we press CtrL+G, to see.
Look at that, so there was nothing in the Address.
Alright, so this is-- this is-- where we're going to use a cool feature called the Object.
Another Watch-- the Watch Window.
I'm going to take the word Target-- I'm going to right-Click on Target-- and say that I want to Add Watch, and I want to watch the expression called Target.
Alright, and what we get here, is a Watch where we can see the Address is in fact blank.
But, I can now browse through here and-- see that Income, that was the word that we clicked on-- right here, this is it-- I'm Looking for the Sheet name and where we're going, and that's in something called SubAddress.
Alright, so, by using the Watch Window, I was able to figure out that I'm Looking for .SubAddress, instead of .Address.
Alright, the Watch Window is very handy when, you know, something goes wrong here, like, you know, where we couldn't figure out where to go.
Alright.
So, at this point, I'll stop this code.
I'll remember that it's SubAddress and not Address, and I'm actually going to pop in some code that I wrote beforehand.
Okay, so, I'm going to set up a variable called LinkTo-- LinkTo = Target.SubAddress.
So, that's going to be Sheet Name, exclamation point, which geek programmers call the Bang.
All right, and I guess I'm a geek Programmer because my next Variable Is called WhereBang-- Where's the Bang.
Do the InStr(1, LinkTo, "!"), and that'll tell me where it starts.
If it's not found, I'll get a 0, so, If WhereBang > 0 Then, I figure out another variable called MySheet = Left(LinkTo, WhereBang - 1).
And we will then make Worksheets(MySheet) .Visible = True.
Select that sheet.
Alright, so, now we've gone to that sheet.
And I have to tell you, the first time I wrote this for Shanna, I stopped at this point because I figured that the Macro would end, and it would go ahead and follow the Hyperlink, but it doesn't.
When you have this event, it doesn't follow the Hyperlink; it just comes here and runs this Macro.
So, now, me, as the writer of the Macro, is responsible for actually doing the Hyperlink.
Very annoying that I had to do that, but hey, you know, took a few minutes and we're good to go.
So, we actually select the sheet-- Worksheets(MySheet) .Select -- and then I had to figure out where the Address was.
So that's the MyAddr = Mid(LinkTo, WhereBang + 1).
I don't have to specify how many characters, because here in VBA-- VBA-- the Mid will just go to the end.
And then-- this was driving me nuts-- so, this was the original Code that I had here, and it would not work.
It kept giving me an error, saying that it was Invalid, and I'd hover over MyAddr-- why?
Tell you what, let's run and I'll show you what was happening to me.
So, I would click on Income-- see Income is not visible right now-- I click on Income and, sure enough, it becomes visible back there.
But, I get this error that's saying that MyAddr = A1 is not valid, and I'm like, "What, how can that not be Valid?" I wrote a new little Macro and tested and, sure enough, it works-- why isn't this working?
I was ready to hand it over to the MrExcel Message board, where the smartest VBA programmers on the planet hang out, to ask them why I'm just so stupid today, and then I realized, Oh, wait a second, this isn't running in a module; this is running on a sheet called Menu and the sheet called Menu doesn't know anything about a cell called A1 out on a Worksheet Called Part1.
So, this line of code had to go and I had it fully qualified so Worksheets (MySheet) .Range (MyAddr) .Select, like, this is a foreign concept to the Macro running on Sheet1.
If we're going to talk about a range on another sheet, we have to fully qualify it, and I'll let that run and that will solve the problem.
Okay, so we're here on Menu-- actually, here I'm going to reveal that in a second-- we're here on Menu, I click on Expense, and it unhides Part2, and takes me to that page, follows the Hyperlink.
To make the Back work, I figured that when they click Back, we need to hide the current sheet.
And so here, let's take a look-- I double-click on Part1, again the FollowHyperlink, and I just hard-coded, Hey, we're always going back to Menu, and then I wanted to take whatever sheet the Target lived on.
So, the Target remembers the Hyperlink that we clicked on-- that's the word "Back"-- if we go to .Parent.Worksheet, that will say the Parent is where the Hyperlink is living, the word Back is lliving in the sheet called Part1, and .Worksheet.Visible = False.
And once that code is working, I had a Control+C that, and then double-click on every sheet that I might potentially be going to, and Ctrl+V to paste.
Now, Shanna has 50 of these-- what a pain to have to go through and do that, but-- we don't want you to do it once, then everything will be working just perfectly.
You click on the Hyperlink, it un-hides the sheet; when they click Back it will hide the sheet.
So, very interesting.
I'd love to see this Dashboard she has with 50 different sheets-- it probably will be the cool hit at her company.
Alright, well, hey, I want to thank Shanna for sending that question in, I want to thank you for stopping by.
Covered a couple of different things there, how to follow Hyperlinks to hidden sheets and also how to use the Watch Window to discover which property has the value that you're looking for.
Great way to browse to figure out which property to use.
We'll see you next time for another netcast from MrExcel.
Learn Excel for MrExcel Podcast, Episode 1729: Hyperlink to a Hidden Sheet.
Hey, welcome back to the MrExcel netcast.
Today's question's sent in by Shanna.
Shanna has a Dashboard Report-- she has a menu with 50 Hyperlinks, and then 50 Worksheets back here, and wants be able to link from the Hyperlink to the Worksheet.
But, here's the thing: The 50 sheets are hidden.
How can you Hyperlink to a hidden sheet?
Well, first, let's set up a Hyperlink here-- so, I'm going to do Ctrl+K.
Now, we're going to link to a place in this document.
It's going to be Part 5, Cell A1.
Click OK, and then on each of the sheets, I imagine there's a Hyperlink that goes back-- it goes back-- to the menu, Cell A1, so that we can jump to a sheet, jump back.
Alright, but right now everything is visible; Shanna has everything hidden.
So, Home, Format, Hide & Unhide, Hide Sheet-- those are all hidden and, now, these Hyperlinks are not going to work, because there's nothing to go to, right?
Very, very frustrating.
Well, what we're going to have to do is, we're going to have to use a little bit of VBA (Alt + F11) in order to un-hide the sheet, so when they click on the Hyperlink we're going to un-hide the sheet, and then, you know, let the Hyperlink work.
So, here's what you do.
You go to Alt+F11.
If you don't see the Project Explorer-- View, Project Explorer, or Ctrl+R. And we want to go to Sheet1, we're going to double-click Sheet1 to get to the code pane for that sheet.
This isn't a regular Macro that's written on a module like this one down here, it's written on the actual sheet's code pane.
Top left Drop-down, there's only one thing to choose, that's Worksheet.
And then, they always choose a SelectionChange by default.
We'll get rid of that-- go to the top right hand Drop-down and there is an event for FollowHyperlink.
FollowHyperlink, okay.
Now, for those of you that are kind of new to VBA, or even those of you who have been around VBA for a while, you know, sometimes you don't remember all of the properties that are available for a Hyperlink.
So, right here they've given us a variable called Target As Hyperlink, and I can't remember what, exactly, I need.
So, x = Target.
alright, and they're offering me Address.
Well, maybe address is what I want-- x = Target.Adress-- but I want to see-- I want to see-- what happens.
So, debug.Print x , alright?
Just to see what's there.
And then, the most important part, I'm going to Stop, so that way the Macro goes into Debug Mode, while this Variable is still valid-- still, you know, alive.
So, now, we'll come back here, we'll click, and that actually-- see that-- runs that Macro, we press CtrL+G, to see.
Look at that, so there was nothing in the Address.
Alright, so this is-- this is-- where we're going to use a cool feature called the Object.
Another Watch-- the Watch Window.
I'm going to take the word Target-- I'm going to right-Click on Target-- and say that I want to Add Watch, and I want to watch the expression called Target.
Alright, and what we get here, is a Watch where we can see the Address is in fact blank.
But, I can now browse through here and-- see that Income, that was the word that we clicked on-- right here, this is it-- I'm Looking for the Sheet name and where we're going, and that's in something called SubAddress.
Alright, so, by using the Watch Window, I was able to figure out that I'm Looking for .SubAddress, instead of .Address.
Alright, the Watch Window is very handy when, you know, something goes wrong here, like, you know, where we couldn't figure out where to go.
Alright.
So, at this point, I'll stop this code.
I'll remember that it's SubAddress and not Address, and I'm actually going to pop in some code that I wrote beforehand.
Okay, so, I'm going to set up a variable called LinkTo-- LinkTo = Target.SubAddress.
So, that's going to be Sheet Name, exclamation point, which geek programmers call the Bang.
All right, and I guess I'm a geek Programmer because my next Variable Is called WhereBang-- Where's the Bang.
Do the InStr(1, LinkTo, "!"), and that'll tell me where it starts.
If it's not found, I'll get a 0, so, If WhereBang > 0 Then, I figure out another variable called MySheet = Left(LinkTo, WhereBang - 1).
And we will then make Worksheets(MySheet) .Visible = True.
Select that sheet.
Alright, so, now we've gone to that sheet.
And I have to tell you, the first time I wrote this for Shanna, I stopped at this point because I figured that the Macro would end, and it would go ahead and follow the Hyperlink, but it doesn't.
When you have this event, it doesn't follow the Hyperlink; it just comes here and runs this Macro.
So, now, me, as the writer of the Macro, is responsible for actually doing the Hyperlink.
Very annoying that I had to do that, but hey, you know, took a few minutes and we're good to go.
So, we actually select the sheet-- Worksheets(MySheet) .Select -- and then I had to figure out where the Address was.
So that's the MyAddr = Mid(LinkTo, WhereBang + 1).
I don't have to specify how many characters, because here in VBA-- VBA-- the Mid will just go to the end.
And then-- this was driving me nuts-- so, this was the original Code that I had here, and it would not work.
It kept giving me an error, saying that it was Invalid, and I'd hover over MyAddr-- why?
Tell you what, let's run and I'll show you what was happening to me.
So, I would click on Income-- see Income is not visible right now-- I click on Income and, sure enough, it becomes visible back there.
But, I get this error that's saying that MyAddr = A1 is not valid, and I'm like, "What, how can that not be Valid?" I wrote a new little Macro and tested and, sure enough, it works-- why isn't this working?
I was ready to hand it over to the MrExcel Message board, where the smartest VBA programmers on the planet hang out, to ask them why I'm just so stupid today, and then I realized, Oh, wait a second, this isn't running in a module; this is running on a sheet called Menu and the sheet called Menu doesn't know anything about a cell called A1 out on a Worksheet Called Part1.
So, this line of code had to go and I had it fully qualified so Worksheets (MySheet) .Range (MyAddr) .Select, like, this is a foreign concept to the Macro running on Sheet1.
If we're going to talk about a range on another sheet, we have to fully qualify it, and I'll let that run and that will solve the problem.
Okay, so we're here on Menu-- actually, here I'm going to reveal that in a second-- we're here on Menu, I click on Expense, and it unhides Part2, and takes me to that page, follows the Hyperlink.
To make the Back work, I figured that when they click Back, we need to hide the current sheet.
And so here, let's take a look-- I double-click on Part1, again the FollowHyperlink, and I just hard-coded, Hey, we're always going back to Menu, and then I wanted to take whatever sheet the Target lived on.
So, the Target remembers the Hyperlink that we clicked on-- that's the word "Back"-- if we go to .Parent.Worksheet, that will say the Parent is where the Hyperlink is living, the word Back is lliving in the sheet called Part1, and .Worksheet.Visible = False.
And once that code is working, I had a Control+C that, and then double-click on every sheet that I might potentially be going to, and Ctrl+V to paste.
Now, Shanna has 50 of these-- what a pain to have to go through and do that, but-- we don't want you to do it once, then everything will be working just perfectly.
You click on the Hyperlink, it un-hides the sheet; when they click Back it will hide the sheet.
So, very interesting.
I'd love to see this Dashboard she has with 50 different sheets-- it probably will be the cool hit at her company.
Alright, well, hey, I want to thank Shanna for sending that question in, I want to thank you for stopping by.
Covered a couple of different things there, how to follow Hyperlinks to hidden sheets and also how to use the Watch Window to discover which property has the value that you're looking for.
Great way to browse to figure out which property to use.
We'll see you next time for another netcast from MrExcel.
