Change Sheets Names – Excel VBA Tips

We all are aware of the basic methods of referencing worksheets in our VBA code. Two of such methods have been illustrated in the picture on the right. But there is one inconvenience in putting these methods to use: the differences between sheets is getting lost in the semantics.

Our very own Jordan Goldmeier (a.k.a. Option Explicit) has decided to solve this issue once and for all. Everything that you need to follow is given right below.

So, let’s get started!

 1 – Properties Window

The first step is to make sure that the Properties Window is visible on your VBA interface. As shown in the image, go to View > Properties Window to enable it.change1

2 – Changing Name

This is the most critical step in implementing the tip. And it’s very easy to follow. Under the properties of the related sheet, you will find one field with title “(Name)”. You can think of this field as the ‘code name’ of the sheet. Change this to something like “Dashboard” because Sheet1, in this example, is the dashboard of my spreadsheet file.


3 – Let’s Test It!

As you can see in the picture on the right, the sheet can be referred to directly as an object using just its name “Dashboard”.change3

4 – What’s the Utility?

Why should one go through all this hassle? Well, look at the sub-procedure defined in this sheet from the picture. Its association with “Dashboard” instead of “Sheet1” gives away a better picture of what the code is doing. The differences between objects of the same type are now not hidden behind the semantics; they are completely explicit.

Get the Download


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.