My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Hide, Delete and Sort Sheets With VBA Userform
Hide, Delete, Sort Sheets with VBA in Excel
by

You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets or just sort them.

By using a very simple userform and some VBA we can do these things with a few clicks of our mouse.

The Form

The forms consist of one ListBox and four Command Buttons:

Form design

The List Box will display the list of sheets in the active workbook, and the buttons do exactly what they say.

Hiding and Unhiding Sheets

Rather than create a button to hide a sheet, I decided to make use of the _DblClick event. By using your mouse and double clicking on the name of a sheet, you hide it. Double click it again to unhide it.

Form design

Deleting Sheets

Click on a sheet name, then click the Delete button. You can select more than one sheet by holding down the CTRL key whilst clicking with your mouse.

Form design

Sorting Sheets

You can sort in ascending order (Sort A-Z) or descending order (Sort Z-A).

Form design

Extra Functionality

It's things like this make your life that bit easier and is exactly what VBA is built for.

This is a simple example of what you can do with a form and only scratches the surface of what is possible.

If you want to really see some cool stuff like :

  • Searching for worksheets and navigating to them.
  • Copying and renaming sheets.
  • Protecting and unprotecting sheets.
  • Moving, organizing, and coloring sheets.
  • Flipping back and forth between two sheets.
  • Jump to the first or last sheet.
  • Moving sheets up, down, first, or last.
  • See how many worksheets are in a workbook?
  • See how many hidden sheets there are.
  • Count how many sheets have a particular tab color.

Then you should check out the excellent Tab Hound which is made by Jon Acampora over at Excel Campus.

The Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Put the code (the form module and the code module) into your PERSONAL.XLSB so you can run it on any workbook.

Then you could add an icon to the QAT so you can run the code with a click of your mouse.

Hide, Delete, Sort Sheets with VBA in Excel

More Userforms Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
add data to combo box drop down list in excel form
If the values in your Excel Form's drop down list aren't sufficient, allow the user to add their own, and save the new value in the list to use again.
Excel forms, insert, update, delete data
Insert, update and delete data in an Excel table with a user form.
excel form data validation
Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel date picker
An Excel date picker (calendar) for all versions of Excel, both 32 and 64 bit. An easy to use VBA class, with examples to download in a workbook
excel userforms
Learn how to create Excel forms for tasks like data entry. Use form controls to gather data, then enter this data into a table on your worksheet.
Excel Form Controls
Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag:

Reader Interactions

Comments

  1. Arnaldo

    Hi guys
    Many thanks for the code and form. Extremely useful.
    However, could you please adivse how I could change the code (or add) so I could be able to hide some worksheets so they wouldn’t show on the list? I’ve tried it using the Case Function but wasn’t very successful.
    Thank you

    Reply

    • Philip Treacy

      Hi Arnaldo,

      If you change the sheet visibility so that it is xlSheetVeryHidden (in the VBA editor) then you can use this modified Sub to only list sheets that are not Very Hidden

      Sub PopulateList()

    Dim counter As Long
    Dim HiddenPrefix As String
    
    SheetList.Clear
    
    For counter = 1 To Sheets.count
        
        If Sheets(counter).Visible <> xlSheetVeryHidden Then
        
        If Sheets(counter).Visible = False Then
        
            HiddenPrefix = "H"
        
        Else
                
            HiddenPrefix = ""
        
        End If
        
        SheetList.AddItem HiddenPrefix & vbTab & Sheets(counter).Name
        End If
    
    Next counter

End Sub
      Reply

  2. Stuart Thompson

    Hi
    This code is fantastic for doing exactly what I wanted.

    I did however modify this slightly so I can select the sheets for hiding and unhiding (the same ways as deleting) in addition to the double click option.

    Also added a Yes/No check when deleting sheets with a message are you sure?

    Thanks for sharing this code

    Reply

  3. Ben M

    Phil,

    Thanks for putting this tool together. It makes things much easier when managing a large workbook with several hidden sheets. I have added it to my Personal.xlsb with a QAT icon and have already used it quite often.

    After playing around with it some, I added a feature that may be of interest to you or others – the ability to see/make “Very Hidden” sheets. As I’m sure you’re aware, there are three Visible properties of sheets. Most people use only the Hidden or Visible properties, but the Very Hidden property will hide a sheet in such a way that it doesn’t show up when right-clicking to unhide sheets. This can be handy when trying to prevent users from unhiding a critical sheet and making changes you didn’t intend.

    The tweak I made to the PopulateList code was to use a Select Case statement on the Sheets.Visible property where Case 0 is Hidden (so returns “H”), Case 2 is Very Hidden (so returns “V”), and Case Else assumes Visible (so returns “”). I then copied the Delete button code and adjusted it to make a sheet VeryHidden so that you can select any sheet and click the button to make it Very Hidden.

    Thanks again for this post!

    Reply

    • Philip Treacy

      Excellent work Ben. It’s great to hear what you have done and this is exactly why I do this. So people like yourself can take my code and tweak/enhance it to suit their own needs and add extra bits of functionality.

      Good job.

      Phil

      Reply

  4. Ron S

    Excellent tip and and tool. A couple of tweaks I’d like to see.

    Column headings in the form: Hidden, Sheet name, Description(?)

    I would like to see the option to add a hidden tab with longer descriptions / comments about the sheet. If a predefined tab name for the comments is found, then a lookup is performed on the tab names to find descriptions.

    Please add a link to tutorial on how to copy this userform and all of it’s associated code to the PERSONAL.XLSB (assuming they don’t have one, like me. I saw your tutorial on creating an PERSONAL, but I don’t see how to move the userform to it. Is that an export, import process?

    PS: add a note to this article reminding people that they could also create a button on their QAT to trigger this form, rather than the button you use in your example. This is handier when sharing the code with other forms where there may not be an appropriate place to put the button. After downloading your file and adding the macro button to the QAT I was able to use the form in other spreadsheets (of course it opens your example sheet in the process, oops).

    I presume, adding the macro from the PERSONAL.XLSB would not launch the sheet?

    Reply

    • Philip Treacy

      Hi Ron,

      All good suggestions. I didn’t include headings because it seemed clear to be what the sheet name was, but you can of course alter the form and quickly add a couple of labels in the right place.

      A hidden sheet with descriptions for each sheet would also be a feature, but would complicate the coding somewhat.

      I will write a separate tutorial on moving code into the PERSONAL.XLSB. You can export/import the code and form modules, or just click on the module in my workbook and drag it into PERSONAL.XLSB, as shown in the animated image towards the bottom of this post Excel progress bar in VBA

      Yes, adding an icon on the QAT is the way to go, and I added a link to my article on doing just that. I overlooked that when I wrote the blog!

      And yes, once the code and form are in PERSONAL.XLSB, running the code won’t open my workbook.

      Thanks

      Phil

      Reply

  5. Jon Acampora

    Great utility Phil. It’s amazing how we can greatly enhance Excel’s functionality with a little bit of code. That’s why I love it. 🙂

    I appreciate you mentioning Tab Hound as well. I use it everyday to search for and jump to sheets in the workbook quickly. It’s like Google Search for worksheets. 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *