Write an Excel Macro in VBA to Update Worksheet Tab Name

Characters Not Allowed in Worksheet Names

Characters Not Allowed in Worksheet Names

This is Part 2 of my two-part series of video tutorials where I demonstrate how to link an Excel Worksheet Tab Name and the contents of a Worksheet Cell.

In this lesson, I show you how to write an Excel Macro in VBA (Visual Basic for Applications) that will update the name of the Worksheet Tab based on the value of a cell in that worksheet.

Write Excel Macro in VBA

For this Macro, the first line of code will prevent the Macro from crashing if the cell contains one of the “disallowed” characters that you see in the diagram

On Error Resume Next

The second line of code initiates a “For – Next” loop of instruction

For Each ws In Thisworkbook.Worksheets

The third line of code is the Instruction to follow in the “For – Next” Loop

ws.Name = Left(ws.Cells(1,5).Value, 31)

Here, this instruction is saying – in plain English – “Nane this Worksheet (ws) using the leftmost 31 characters in the cell that is in the 1st row of the 5th column – (Cell E1)”

The reason for including 31, is that that is the maximum number of characters allowed in an Excel Worksheet Tab Name.

The fourth and fifth lines of code complete the macro – watch the video to see what they are (What a “tease” I am!!)

Run Macro from Command Button

In this lesson I also show you how to run this Macro from a command button that I add to the Quick Access Toolbar in Excel 2007 or Excel 2010.

Watch Video in High Definition on YouTube

 Click on this link to watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Source for This Code

I found the code for this example in one of Bill Jelen, Mr. Excel’s Books, “Excel Gurus Gone Wild.”

Bill’s website is: http://www.mrexcel.com/

Watch Part 1 of this Series

Here is the link to Part 1 of this series. I show you how to write a Custom Excel Function to insert the Name of the Worksheet Tab into a cell on the worksheet.

The versatile and valuable =IF() Function

MS Excel’s =IF() Function is a very versatile function and it is used in a variety of situations. Watch this short video lesson to learn how to use it when Grading Scores, Calculating Shipping Rates and “Trapping Errors.”

Here are the steps to follow in this lesson:

  1. The =IF() Function requires three arguments: A “Logical Test” – (which will return either a TRUE of a FALSE Response); the Value if “True,” and the Value if “False.”
  2. In my first example I have set up a simple scoring system for grades. For the Value if TRUE, I have entered “Pass.” Notice that the TEXT must be enclosed in ” “.
  3. In my second example, I have “nested” several =IF() functions inside the Value if FALSE Function Argument. You can “nest” up to 7 different IF() Functions in a formula.
  4. Notice how useful the =IF() Function is when you want to “trap an error.” Especially when you are starting to build a spreadsheet and many cells contain Zeros or NULL Values.

Find the Excel Video Lesson that you want – Index to All Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.