How to Automate Excel with Tab Control - Excel Campus
11

How To Automate Common Processes in Excel with Tab Control

In the last two videos I explained how the Tab Hound and Tab Control add-ins can save you a ton of time with your everyday Excel tasks.  Here are links to the two videos in case you missed them.

Video #1 – Quickly Find Worksheets & Unhide Multiple Sheets

Video #2 – The Fastest Way to Copy a Sheet – Plus Tab Control

These videos also contain a lot of Excel tips and shortcuts, and I hope you can learn something from them.

In this new video I am going to demonstrate how you can automate some common tasks with the Tab Control add-in.

You will learn:

  • How to add multiple sheets to your workbook from a list of sheet names.
  • How to re-order your sheets quickly with drag-and-drop.
  • How to automate the process of hiding and unhiding multiple sheets for a monthly reporting package.

I also answer questions from the last video, including when Tab Hound and Tab Control will be available.

The Future of Tab Control

In the video above I give a simple example of how Tab Control can be used to automate the process of hiding and unhiding sheets.    This is a common process that many of us do for periodic reports.

This is just one example of how Tab Control can be used for process automation.  It really only scratches the surface of what is possible with this tool.

Using a spreadsheet (TabControl sheet) for the inputs gives us a lot of possibilities for different features and uses.  Based on your suggestions, I’m currently working on ways to split (export) sheets into different files, apply detailed protection properties, copying sheets, and more.

I’m looking forward to learning how you use this tool.  I know we will be able to create ways to help automate some of your repetitive tasks.

Please leave a comment below with any questions or suggestions.

Thank you! 🙂

Tab Hound and Tab Control are Now Available

The Tab Hound and Tab Control Add-ins are 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
Sohail Baig - August 21, 2014

Thank you for sharing wonderful knowledge. Things you sheared really helping a lot.
I m here to submit a request if I may
I m looking for a complete application built in Excel for example Sales Excel
Sheet 1 content only buttons like –
Add new sale – opens up a form to add a sales
Search – same as you have Find All tutorial
And anything you can add

This way not only we all will learn the complete excel workflow also will make us understand how we can implement our daily task in excel
Thank you and looking forward for your early and favorable response

Reply
Wes - August 1, 2014

Jon
Some time I need to copy items in a worksheet to another workbook that contain formulas, this is what usually happens.
The range you are pasting contains formulas that cannot be pasted into this instance of excel.
I use excel to collect data from a data accusation system in a power plant, excel don’t like to copy and paste the formulas from one book to another.
I most often have to go in and manually write the formulas into the new workbook.
Can tab hound be setup to copy the formulas to other workbooks?

Reply
    Jon Acampora - August 12, 2014

    Hi Wes,
    You can get that error message if you have multiple instances of Excel open at the same time, and you are trying to copy data from one instance to another. It can be difficult to tell when you have multiple instances open. What version of Excel and Windows are you using? I can provide some info or screenshots that will help you prevent this problem.

    Thank you,
    Jon

    Reply
Jeff Weir - July 31, 2014

Hi Jon. Looking good. Suggestion re your goal to do things the fastest way with least amount of actions: Do away with the need to click the Update wherever it makes sense, and do away for the need for the user to dismiss the ‘Done’ message. Especially for things like reordering the table, or when the user selects from the dv list. Just put an event handler on the TabControl sheet…if something gets changed, run the update. Just add a ‘defer update’ option in case a user actually has the sheet open and is making multiple changes.

Re the “Done” messages: For me they are largely redundant – I know the sheets were reordered or whatever because i clicked the button, and in fact can review the sheet tabs if I really want. Having to dismiss a “Done” message forces me to click or push Enter.

Saves two clicks overall. And also suggest you expose the functions to VBA developers so they can just call these in need as part of other routines. (Maybe you have).

Reply
    Jon Acampora - July 31, 2014

    Thanks for all the great suggestions Jeff! I agree with the redundancy of the “done” messages. I’m planning to add some Options where you can turn those off.

    I like your idea of using the event handler for any sheet changes. That would definitely make it faster!

    Thanks again. I love to save clicks… 🙂

    Reply
Peter Raiff - July 31, 2014

Brilliant!! Just Brilliant!

Reply
Dan - July 31, 2014

Loved the new feature Tab Control. I noted that you set up the link to work quarters and that is awesome however what about semi-annual or annual reports? Would one just extend the pull-down to include the other options (i.e., 1st half, 2nd half, ALL)

Reply
    Jon Acampora - July 31, 2014

    Great question Dan. Everything in that Tab Control sheet was formula driven. And those formulas can be completely customized by you. Sorry, I don’t know that I made that clear in the video.

    But you could put any formula in column C (Visible) of the Tab Control window. The formula just has to return a TRUE or FALSE value.

    That means you could definitely have formulas for 1st Half/2nd Half. You could also set it up to accommodate any time period like weeks, months, etc. You could also set it up for different categories or regions. The possibilities are endless and it is can be completely customized by you.

    I will create more instructional videos to walk through the process in more detail. I’m planning to have a dedicated web page for Tab Control where you can leave comments to ask questions, and also see what others have asked.

    Reply
Dan Franceski - July 31, 2014

Jon –
It’s looking better all the time.
I almost always want the footers in my printed tabs to have &Filename;&tab at the left, &page of &pages in the center, and printed &date &time at the right.
I know how to copy those settings from an exiting sheet to another by selecting both tabs, going to the ribbon for Page Layout, clicking the little arrow at the bottom right and then OK. Do you think adding “Copy Page Layout” feature as a one-click to Tab Hound is a good idea?

Reply
    Jon Acampora - July 31, 2014

    Hi Dan,
    That’s definitely a good idea. Tab Hound has a select sheets button that allows you to select multiple sheets. So you could use it to select the sheets quickly before copying the page layouts. But I like the idea of having a dedicated button. I’ll put it on the list… 🙂

    Reply

Leave a Reply: