Excel/VBA Active sheet: Learn to Access, Rename, Activate with 4 Examples
In this tutorial, how to make a non-active sheet in Excel an active sheet by using VBA code.
Working with Excel Active Worksheet using VBA
In this tutorial, you will learn how to make a non-active sheet in Excel an active sheet by using VBA code.
The examples will cover how to access an active sheet name, rename an active sheet, and make a sheet active and removing as well.
For the information, if a sheet is active, the actions you perform like macro actions, accessing data, etc. without referring to a sheet name/number will be performed on that active sheet.
An example of getting the name of Active Sheet in VBA
Let us start with a simple example of getting the active sheet name and displaying it in a message box.
Our sample Workbook has the following sheets:
In VBA, the Application object has a property ActiveSheet which enables us to work with the active sheet.
The ActiveSheet has a method called “Name” that can be used to get the active sheet name.
Display active sheet name in alert example
Step 1:
Place a command button in a sheet:
Step 2:
Double-click on the button in “Design Mode” to open the VBA editor
Step 3:
Write this line of code in the click event of the button:
MsgBox "Active Sheet Name = " & ActiveSheet.Name
Save the code and get back to the sheet containing that button.
Step 4:
Click on the button after unselecting “Design Mode” and it should display our active sheet name in an alert as shown below:
Making a sheet active in VBA example
To make a sheet active in VBA, you may use the Worksheet object with its Activate method.
Suppose, we were on the “test1” sheet in our sample Workbook. We want to make “Sheet5” active. This is how you can do it:
Worksheets("Sheet5").Activate
Let us have a look at this visually.
Step 1:
We placed a command button on the “test1” sheet.
Step 2:
Double-click the button to open the VBA editor and write these lines of code on the click event of button:
'Display Active sheet name before Activate method
MsgBox "Active Sheet Name = " & ActiveSheet.Name
Worksheets("Sheet5").Activate
'Display Active sheet name after Activate method
MsgBox "Active Sheet Name = " & ActiveSheet.Name
Save the code.
Remember to Select “Design Mode” to open the editor.
Step 3:
Click on the button and it should display two message boxes. First, the current active sheet name i.e. “test1” in our sample sheet.
Then this line of code executes:
Worksheets("Sheet5").Activate
And again a message box displays that shows the following:
Renaming the Active sheet example
For renaming the active sheet, simply use the ActiveSheet.Name i.e.
ActiveSheet.Name = "New Name"
To demonstrate that, consider our sample Workbook shown in the first example, let us change the name of the active sheet.
“Sheet5” is our active sheet and we want to rename it to “Product Information”. See below how.
Step 1:
Place a command button in Sheet5 and open the VBA editor by double-clicking on the button.
Step 2:
Write this code on the click event of the button:
'Sheet Name before MsgBox "Active Sheet Name = " & ActiveSheet.Name ActiveSheet.Name = "Product Information" 'Sheet Name after MsgBox "Active Sheet Name = " & ActiveSheet.Name
Save the code.
Step 3:
Click the button and it will change the Sheet5 name as shown below:
Removing the Active sheet example
Removing the Active sheet is also simple. Just use:
ActiveSheet.Delete
method and active sheet will be removed.
For example, we wrote this code line to the command button click event. As you click the button, Excel will trigger a warning message.
Pressing Delete will remove the active sheet, in our case “Product Information” sheet as shown below:
