How To Organize & Manage Your Worksheets - Tab Hound & Tab Control - Excel Campus
11

How To Organize & Manage Your Worksheets – Tab Hound & Tab Control

In the last video we learned how Tab Hound can save time with common tasks like finding and jumping to sheets, flipping between sheets, and unhiding multiple sheets.

I really appreciate all your suggestions and support for Tab Hound.

In this video I am going to explain:

  • Shortcuts for copying and renaming sheets.
  • A fast way to copy sheets to a new workbook.
  • How to create a Table of Contents in your workbook.
  • How to organize and manage your worksheets with Tab Control.

I also address some of your questions from the last video, so check it out!

Video

Please leave a comment below letting me know what you think of Tab Hound & Tab Control.

Tab Hound and Tab Control Logo

Why Tab Control?

As I started adding more features to Tab Hound, I quickly realized that I wanted it to be able to automate more of the complex tasks I do with my worksheets.  This included processes like:

  • Renaming multiple sheets at one time.
  • Re-ordering the sheets in the workbook.
  • Changing the colors of all the tabs quickly.
  • Hiding and unhiding sheets based on some criteria like the current month.
  • Adding a bunch of new sheets with specific names and tab colors.

Some of these were possible in a form like the Tab Hound window, but it is difficult to develop in a way that is easy for the user.  My goal is to make these tools very easy to use.  So that led me to use a worksheet instead.

Having the sheet list in the TabControl worksheet makes it very easy to work with.  You can quickly rename the sheets in the cells, change the tab colors with the fill color, sort and re-order the list with the Filters, etc.  And you can do this in an environment that you are very familiar with (the Excel worksheet).

In this video I only scratch the surface of what is possible with Tab Control.  There are a lot of possibilities with this tool, and I can’t wait to see what you will do with it.

In the next video I will show examples of how I use Tab Control to automate some monthly reporting packages.  I am also going to give more info on when Tab Hound and Tab Control will be available.

What Do You Think?

I really appreciate you taking the time to leave a comment with your thoughts about these tools.  Your suggestions are great, and it’s nice to learn more about how you are using Excel.

Thank you! 🙂

Please watch the next video in this series to see a real world example of how you can use Tab Control to automate your processes.

Tab Hound is Now Available

The Tab Hound Add-in is now available.  Please click the link below to learn more about it.

Tab Hound Box 150x150

Get Tab Hound

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 11 comments
Ian Wainwright - July 26, 2014

Hi Jon. I really enjoyed the video and it looks like the tool’s going to be a great help to many spreadsheet users.

I did have a couple of thoughts. There should be a duplicate name check to the tab control list to avoid using the same name more than once. How about also adding a tab colour column to tab hound’s worksheet list window.

Hope these make sense and I look forward to seeing the finished product.

Reply
    Jon Acampora - July 28, 2014

    Thanks Ian! And thanks for the great suggestions! Tab Control does check for duplicate names before running the update. It also checks for invalid characters in the sheet name and for sheet names that are over the 31 character limit.

    The color column in the Tab Hound window is a great suggestion. It’s not easy to do in a standard VBA listbox, which is what I’m currently using for the sheet list in the Tab Hound window. But it might be a possibility someday. That was really why Tab Control was created. It is much easier to display and work with colors in a worksheet versus a form.

    Thanks again!

    Reply
John - July 25, 2014

Jon,

The flick back for me is a great feature but the Table of Contents, well thats just taken TabHound to another level!

Im sure that this will help heaps of people using Excel.

I cant wait to get my hands on it.

Cheers,

John

Reply
    Jon Acampora - July 25, 2014

    Thanks again for the nice comments John! I can’t wait to get it out into the world as well. I know that Tab Hound will improve and evolve as everyone starts using it.

    I’ve received some great suggestions for the Table of Contents. I will be adding a way to add backlinks to each sheet, so you can quickly jump back to the TOC sheet from any sheet in the workbook. That feature will probably be in a future version of Tab Hound. I want everyone to have a chance to actually use it first… 🙂

    Reply
Frank - July 24, 2014

Could you add the ability to sort the sheets in the workbook itself (by name/color) ?

Reply
    Jon Acampora - July 25, 2014

    That is definitely possible in the Tab Hound window. Currently it is possible with Tab Control, along with a lot of other sorting features. The alpha sort of the sheet order is an easy enough button to add to the tab hound window. I’ll put it on the request list… Thanks for the suggestion Frank!

    Reply
Jim - July 24, 2014

You have copy & rename for sheets. Is it possible to have copy to new book & rename? This would make both copy functions the same.

Reply
    Jon Acampora - July 25, 2014

    Yes, that is definitely possible. There is also a rename button that allows you to just select multiple sheets and rename them all at one time. That would be a two step process, since you would first have to copy the sheets to the new book. But still faster than doing it manually.

    Reply
    Jon Acampora - July 25, 2014

    Typically when I’m copying a sheet(s) to a new book, I don’t need to rename it. But when I’m copying a sheet within a book, then I need to rename it because it is going to server a different purpose. That is why the functionality of the buttons are different. But I realize that is just me, and every has different processes.

    I know that there are going to be a lot of requests for different buttons and functions, and I am very excited about that. I think we will all learn a lot from each other and become more efficient in the process. I’m working on ways to add more buttons to the Tab Hound window without making it a giant form. Those features will be in version 2.0 though. I want to get it in your hands first, and then you will really have some feedback… 🙂

    Reply
Jim - July 24, 2014

When Tab Hound shows is sheets are visible or hidden, does it also show very hidden sheets?

Looking forward to the release of Tab Hound.

Thanks

Reply
    Jon Acampora - July 25, 2014

    Thanks a great question Jim! Currently Tab Hound ignores all “very hidden” sheets. This was an intentional decision. My thought was that the creator of the workbook is making sheets very hidden so that the readers/users do not find or modify them. If those users were using Tab Hound then they would be able to see the very hidden sheets.

    With that said, I am working on a way for Tab Hound to work with very hidden sheets. I have had a few requests for this. I agree that it is time consuming to go into the VB Editor to change the visible state of the sheet(s) to very hidden. And Tab Hound is the perfect tool to bring that functionality into the Excel file so you don’t have to open the VB Editor.

    The solution might be as simple as putting a checkbox on a Tab Hound Advanced Options window to “show very hidden sheets”. Then the creator of the workbook could password protect the VB project if they did not want users to gain access to the very hidden sheets. This is just a theory right now, but seems possible. Let me know if you think of any other ways to handle this. I’m definitely open to suggestions. 🙂

    Thanks for the question!

    Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x