Thankyou so much. This was very helpful. I just have one question. For the ShowSelSheets macro, if I want to make both Menu and Instructionsheet visible?
Great video thanks. In my file, I have worksheet tabs, but also tabs containing stand alone charts. The macro throws an error when I have chart tabs currently, as the are mot considered worksheets - could you advise on how to change the macro so I can bring up certain worksheets and charts? Any assistance you can provide on this would be much appreciated.
Thanks for your question, Emily. I've added a new sample file on the instruction page, and you can find it in the download section (Single Menu-Charts) www.contextures.com/excelworksheetsshow.html
Hello :) Amazing Video, i just have a quick question! So I need the sheets to be as per the weeks. so when I insert Week 1, It also shows Week 10, 11, 12 etc... as the number 1 is included in all of these number. Is there a workaround to avoid this ?
Hi, I know this is an old post but really handy macro code. Anyways, what if the worksheets are protected and would like to unprotect them when I selet sheet type/list? Been searching for specific code but no luck. hope you can still help me or any one there have idea. Thanks.
Raymond, you can add a line near the top of the "For Each ws" section, to unprotect, then another line near the end, to protect again. ws.Unprotect Password:="yourpwd" other code here ws.Protect Password:="yourpwd"
@@contextures Thank you for this lesson - really helpful for large workbooks - could you please add Workbook protection and Worksheet protection code - I have tried your suggestion - first part works to select sheet event, but, when clicking ALL - I receive an error - Thanks M
Is something like this possible but with certain excel tables? I mean, that I would choose a value in the dropdown list and it would show me a certain table, that was not visible before.
what if i have two drop down list..and besides menu sheet visible, what if menu sheet and instructions sheet both of these make two sheets make visible?what should i type or add in macro? thx..
A great video. I followed the instructions and can see all the tabs or none of the tabs but cannot select individual tabs to view. I compared my macros with the macros in UF0034 and they are identical except for the tab names. What am I doing wrong?
Hallo. I have written a macro to automatically add and rename worksheets by clicking a button in a Userform. I want the combo box in the Userform to automatically add worksheets names in the dropdown list when I create them so that I can run the macro to hide or unhide the selected sheet from the combo box drop down list. How can I do it?
thanks for the video! I am wondering if I want the sheet dropdown list in a main sheet(once I open the file, main sheet always is active sheet), dropdown list includes all other sheets, once I select one sheet from the dropdown sheet(main sheet), I would like that sheet is active sheet, so I could edit it . for example, I have 5 sheets , from the dropdown list on main menu, I could see 5 sheets' name as sheet1, sheet2,...., if I select sheet1, the active sheet will become sheet1, so I could edit any data on the sheet1. from the each sheet#, I could also go back to make sheet which has dropdown sheet list. same thing, once I get one sheet, there is dropdown list for different areas, I could select any area to process. ... thanks rose
Thanks Aidan, and I just tested it in Firefox, and the file opened. Could you try the download again? Occasionally a file gets corrupted while downloading.
Google Sheets uses a completely different type of coding - one which I am not familiar with. Sorry. Perhaps you can find a GAS message board and see if they can help Reddit has an excel sub but I'm not sure if they have a google sheet sub, but check it out and you might get lucky
The video missed 2 VERY important steps! I watched it 4 times just to make sure I was not missing them. On the Admin_Lists tab: You need to name the table you created as SheetTypes. To give the table the name, select all of the options you currently have (except the header), and where it shows Table1, type the name SheetTypes. On the Menu tab: Select cell C3 as this is where the Data Validation List will be and name it SelectType. Click on Data on the top and select the Data Validation option. Make sure you have the Allow set to List and type =SheetTypes as the Source. Click Ok
Siddeek from KSA, Please help me in this movement so this will help for many of them. I hope In Excel VBA can do that when ever opening excel application there has to open user form that user form consist Name ID No and login time while finishing work logout time has to enter automatically in a specific folder. further all the details should be hidden only allowed to see supervisor and that folder has to consist a password as well and the sheet has to be veiled I hope you won't distinguish this request Thank and God bless you Siddeek
I'm looking to unhide sheets in Excel using an Input Box instead of the Drop-down list. Below is the code I have so far, however I want to modify the macro to use an Input Box to choose the specific text to look for. Can someone point me in the direction either to a video or open-source code? Sub Unhide_Sheets_Containing() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If InStr(ws.Name, "pivot") > 0 Then ws.Visible = xlSheetVisible End If Next ws End Sub
This code would prompt you to enter text: Sub Unhide_Sheets_Containing() Dim ws As Worksheet Dim MyText As String MyText = InputBox("What text?", "Enter Text") For Each ws In ActiveWorkbook.Worksheets If InStr(UCase(ws.Name), UCase(MyText)) > 0 Then ws.Visible = xlSheetVisible End If Next ws End Sub
Thank you! I've been looking for something like this for ages.
2021 still relevant
Thankyou so much. This was very helpful. I just have one question. For the ShowSelSheets macro, if I want to make both Menu and Instructionsheet visible?
In the macro, change this line
If ws.Name "Menu" Then
to this:
If ws.Name "Menu" And ws.Name "Instructions" Then
Great video thanks. In my file, I have worksheet tabs, but also tabs containing stand alone charts. The macro throws an error when I have chart tabs currently, as the are mot considered worksheets - could you advise on how to change the macro so I can bring up certain worksheets and charts? Any assistance you can provide on this would be much appreciated.
Thanks for your question, Emily. I've added a new sample file on the instruction page, and you can find it in the download section (Single Menu-Charts)
www.contextures.com/excelworksheetsshow.html
could you make full video, from start to the end, I do not understand how you me selectType list
Thanks for the brilliant guide. Just a question, how do i input in macro to select that particular sheet after unhiding it?
Hi nice explanation! I am getting error Sub or function not defined (in sellected area ShowAllSheets)
If i want to open multiple worksheets by drop down ,how to achieve that ?
Hello :)
Amazing Video, i just have a quick question!
So I need the sheets to be as per the weeks.
so when I insert Week 1, It also shows Week 10, 11, 12 etc... as the number 1 is included in all of these number.
Is there a workaround to avoid this ?
How to show two sheets in this code ?
Amazing but I am getting complier error at below line.
If InStr(1, ws.Name, strType) > 0 Then
Hi, I know this is an old post but really handy macro code. Anyways, what if the worksheets are protected and would like to unprotect them when I selet sheet type/list? Been searching for specific code but no luck. hope you can still help me or any one there have idea.
Thanks.
Raymond, you can add a line near the top of the "For Each ws" section, to unprotect, then another line near the end, to protect again.
ws.Unprotect Password:="yourpwd"
other code here
ws.Protect Password:="yourpwd"
@@contextures Thank you for this lesson - really helpful for large workbooks - could you please add Workbook protection and Worksheet protection code - I have tried your suggestion - first part works to select sheet event, but, when clicking ALL - I receive an error - Thanks M
Is something like this possible but with certain excel tables? I mean, that I would choose a value in the dropdown list and it would show me a certain table, that was not visible before.
what if i have two drop down list..and besides menu sheet visible, what if menu sheet and instructions sheet both of these make two sheets make visible?what should i type or add in macro? thx..
A great video. I followed the instructions and can see all the tabs or none of the tabs but cannot select individual tabs to view. I compared my macros with the macros in UF0034 and they are identical except for the tab names. What am I doing wrong?
Thanks, Eric. On the Admin_Lists sheet, do the entries exactly match the text you're looking for in the sheet names, including upper and lower case?
I got it to work, very nice. I really don't know anything about VBA but this helped a lot. Thanks!
Great! Thanks for letting me know that it's working now.
Hallo. I have written a macro to automatically add and rename worksheets by clicking a button in a Userform. I want the combo box in the Userform to automatically add worksheets names in the dropdown list when I create them so that I can run the macro to hide or unhide the selected sheet from the combo box drop down list. How can I do it?
Please let me know how i can jump from one sheet to another sheet quickly. for example for sheet A to Sheet Z..
Right click on arrow, worksheets will appear
thanks for the video!
I am wondering if I want the sheet dropdown list in a main sheet(once I open the file, main sheet always is active sheet), dropdown list includes all other sheets, once I select one sheet from the dropdown sheet(main sheet), I would like that sheet is active sheet, so I could edit it . for example, I have 5 sheets , from the dropdown list on main menu, I could see 5 sheets' name as sheet1, sheet2,...., if I select sheet1, the active sheet will become sheet1, so I could edit any data on the sheet1.
from the each sheet#, I could also go back to make sheet which has dropdown sheet list.
same thing, once I get one sheet, there is dropdown list for different areas, I could select any area to process.
...
thanks
rose
did u find answer for what u are looking for?
Thank you for this video,.. Everything I had done good, but when i am select " ALL", it doesn't shows all tabs. please assist me to overcome this?
when you click "view code", there is a space in front of the " All" .. Just remove the space and it works
Thank you but your corresponding zip file does not open. I can open your other zip files but not the one I need for this.
Thanks Aidan, and I just tested it in Firefox, and the file opened. Could you try the download again? Occasionally a file gets corrupted while downloading.
Contextures Inc. Thank you..I was using chrome. Will try again. Thanks again..Very helpful video.
Does anyone know if there's a way to do this in Google Sheets?
Google Sheets uses a completely different type of coding - one which I am not familiar with. Sorry.
Perhaps you can find a GAS message board and see if they can help
Reddit has an excel sub but I'm not sure if they have a google sheet sub, but check it out and you might get lucky
Mine is not even firing... not sure what am I doing wrong...
The video missed 2 VERY important steps! I watched it 4 times just to make sure I was not missing them.
On the Admin_Lists tab:
You need to name the table you created as SheetTypes.
To give the table the name, select all of the options you currently have (except the header), and where it shows Table1, type the name SheetTypes.
On the Menu tab:
Select cell C3 as this is where the Data Validation List will be and name it SelectType.
Click on Data on the top and select the Data Validation option. Make sure you have the Allow set to List and type =SheetTypes as the Source. Click Ok
Siddeek from KSA, Please help me in this movement so this will help for many of them. I hope In Excel VBA can do that when ever opening excel application there has to open user form that user form consist Name ID No and login time while finishing work logout time has to enter automatically in a specific folder. further all the details should be hidden only allowed to see supervisor and that folder has to consist a password as well and the sheet has to be veiled I hope you won't distinguish this request Thank and God bless you Siddeek
Please send this download file
I really appreciate this. thanks!
Muchas gracias , súper claro y útil...
You're welcome, Rafael!
Very handy tip
Thank you, sartaj!
good, simple and informative
Thank you!
thanks for the sharing.
I'm looking to unhide sheets in Excel using an Input Box instead of the Drop-down list. Below is the code I have so far, however I want to modify the macro to use an Input Box to choose the specific text to look for. Can someone point me in the direction either to a video or open-source code?
Sub Unhide_Sheets_Containing()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If InStr(ws.Name, "pivot") > 0 Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub
This code would prompt you to enter text:
Sub Unhide_Sheets_Containing()
Dim ws As Worksheet
Dim MyText As String
MyText = InputBox("What text?", "Enter Text")
For Each ws In ActiveWorkbook.Worksheets
If InStr(UCase(ws.Name), UCase(MyText)) > 0 Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub