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.

change2 

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

Jamani Arsalan

Tags


You may also like

April 23, 2014

A VBA Coding Manifesto (Part 1)

May 3, 2014

A VBA Coding Manitesto (Part 2)

December 22, 2013

Excel Holiday Card
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

>