How to Customize the Quick Access Toolbar in Excel 2007

Quick Access Toolbar Office 2007

QUick Access Toolbar in Office 2007

Learning how to Customize the Quick Access Toolbar (QAT) in Excel 2007 or Excel 2010 is a great way to ease the “learning curve” when you upgrade versions.

In my experience, I have found that most people do not even realize that there is a Toolbar in Excel 2007, let alone that you can move it to a different location and to customize it!

What I Cover in this Video Tutorial

Here is what you will learn from this YouTube Video Tutorial:

  • To move the QAT to Show it Below the Ribbon (recommended!)
  • To add the most popular command buttons one at a time
  • To open the Customize Quick Access Toolbar Dialog Box
  • To find commands to add – including Command Not in the Ribbon!
  • To organize the commands on your QAT
  • To create a customized QAT for “specific workbooks”  – great for special projects!
  • To quickly add individual command buttons with a right-click of the mours
  • To add entire command button groups to the QAT with a right-click of the mouse – (recommended!)

Since this video is a little longer than usual, I am making it available exclusively on my YouTube Channel.

Watch Video in High Definition Mode

Follow this Link to view this tutorial in High Definition on my YouTube Channel – DannyRocksExcels

“The 50 Best Tips for Excel 2007” DVD-ROM

Purchase my DVD-ROM, “The 50 Best Tips for Excel 2007” – on sale now at http://shop.thecompanyrocks.com for only $29.97 USD!

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.