The video this question came in from one of our viewers via email and what he wants to know is how to hide all the worksheets in a workbook except.
For the selected worksheets what makes this question a little tricky to answer is the fact that there might be more than one worksheet selected this would be much easier to do if there.
Was only a single worksheet selected or even if we wanted to do the reverse and hide all of the selected sheets keeping the unselected ones visible i do have a solution i suppose i wouldn't be.
Making the video if i didn't i'm not entirely sure i'm happy with it but let's take a look and see what you think to get started i've created a new blank.
Excel workbook and just added a few extra worksheets to it so i've got 10 in total i've also gone into the visual basic editor and created a new module.
That allows you to reference all of the selected sheets it's not immediately obvious how to access the property that returns the selected cheats collection however.
I'm going to try to loop through the collection by declaring a variable that holds a reference to a worksheet object so i'm going to say dimws as worksheet then i'm going to say for.
Each ws in now if i just wanted to loop through all of the worksheets in the workbook i could say something like this workbook dot worksheets.
There is a collection called selected sheets but to access it i've got to refer to a window object first so from this workbook.
I'm going to refer to the windows collection i only have one window available for this workbook currently so i'm going to refer to the first window in the collection.
And then i can find the selected sheets
Property in there that returns a reference to the sheets collection for all of the selected sheets i'll close off my loop by saying next ws.And then inside the loop i'll say debug.print ws.name i then want to make sure that i've got multiple worksheets selected so let's just quickly switch back to excel.
I'll hold down the control key and just randomly select a few other worksheets and then if i head back to the visual basic editor and run that subroutine i'll find i get a list of all of those.
Worksheet names now it would be really helpful if there was a corresponding unselected cheat property but sadly of course there isn't so we have to work around this somehow.
The approach i'm going to take is first of all to loop through the entire worksheets collection and make each worksheet hidden except for the active sheet.
The active sheet is fairly easy to reference even when you have multiple worksheets selected there's still only one active one so in this case it's sheet.
One it's got a slightly different colored uh text on its tab compared to the other sheets in the selection so what we'll do first of all back in the visual basic.
Editor is add a new loop above this one that says for each ws in this workbook dot worksheets and then i'm going to close off my loop.
By saying next ws and then what we're going to do is check check if the worksheet referenced by this variable is not.
The active sheet so i'm going to say if
Not ws is active sheet then i'll give myself a blank line and close off my if statement with an end if.As long as that condition is true i'm going to say ws dot visible equals excel sheet hidden now the key to this working is that even when we hide worksheets.
The selected sheet property still returns a reference to all of the selected sheets so what i'm going to do is just clear the contents of the immediate window.
And then head back to this subroutine and execute it and we should see that we list out all of the selected sheet names one four eight and ten.
And back in the visual sorry back in the excel window we have everything hidden except for sheet one which was the active sheet so what we can do next back in the.
Visual basic editor is rather than just printing out the names of those worksheets we could make the selected sheets visible again.
So rather than just debugged or printing things we can say ws.visible equals excel sheet visible having done that i'm going to run the subroutine again and.
We should see when we get back to excel that all the sheets we previously had selected are now made visible again it might also be helpful to have a.
Separate subroutine to unhide all of the currently hidden sheets so to do that we can head back to the visual basic editor and let's create another subroutine.
Called show all sheets the simplest way to achieve this is to loop through the entire worksheets collection making each worksheet visible property equal to excel sheet.
Visible so we can just copy and paste the loop from the original subroutine that loops through the entire worksheet's collection.
We don't really need to test anything about each worksheet the if statement can be removed entirely it doesn't matter if we try to make visible a worksheet that is already.
Visible that will have no effect so let's just change the visible property of every single worksheet to excel sheet visible and having done that we can run the subroutine again.
And all of these worksheets will now become visible then maybe just for testing we could select a different range of sheets and then run the original subroutine to.
Make sure that all of the worksheets become invisible except for the ones we had selected there's one extra thing about changing the visibility of worksheets that's.
Probably worthwhile knowing just before we wrap up the video so of course if you've hidden worksheets it's simple enough to right click on any existing.
Sheet tab choose the unhide option and then select the worksheets you want to make visible again when you click the ok button the selected sheets will become visible.
But what if you wanted to prevent that from happening what if you wanted to be able to prevent your users from right-clicking to unhide sheets.
Let's head back to the visual basic editor and first of all i'm going to run the subroutine to select all of the sheets or show all of the sheets and then.
In the original subroutine the hide all but selected sheets subroutine i'm going to change the visible property to the value excel sheet very hidden.
So having done that i can head back to the excel window let's select a few different worksheets and having done that head back to the visual basic editor.
Run the hide all but selected sheets subroutine again and we'll end up with the sheets we had selected initially but if we now right click on the.
Existing sheet tab there apparently are no worksheets to unhide so when a worksheet is made very hidden you can't unhide it simply by right-clicking on a sheet tab.
And choosing the unhide option of course we can still run our subroutine to show all sheets and that will bring everything back but it prevents your end user from changing.
Things you might not want them to change so i hope that's enough to answer the original question i think it is if not feel free to carry on asking more questions as always and i'll do my best.