Logo
×

VBA ActiveSheet

What is VBA Activesheet Property?

The active sheet means the current worksheet which you are working on and viewing. The ActiveSheet object signifies the worksheet tab that is selected before running the VBA code. If the user is working with multiple sheets, then the currently viewed files are considered as the active sheet. VBA facilitates many properties and methods that can be called using the ActiveSheet object.

By Default, Excel considered the only topmost worksheet as the ActiveSheet, and all the VBA coding is applicable only for the topmost worksheet. The user can mark or align any Excel worksheet as the Active sheet by calling the Activesheet object with the sheet name. One can also use the VBA Activate method to activate any Excel sheet.

People often confuse the Active worksheet with the selected sheet and use them interchangeably. But both have different functionalities.  Let’s evaluate the difference below:

Selected Worksheet  Active Worksheet
Selected Worksheet can select one or more Excel Worksheets within an Excel Window.   Active Worksheet only selects the current Worksheet to view and work upon.  
Each Workbook can have multiple Selected Worksheets At a time, only one active sheet can be selected.

Importance of Using ActiveSheet Object

  1. This property is useful when a user wants to use another sheet (to run the VBA code and modify the objects apart from the topmost worksheet.
  2. Many times we automate an Excel task with the help of multiple worksheets. In this situation, we can use Activesheet property to set the Activesheet variable and practice it for future reference.

Syntax

expression.ActiveSheet

where the expression variable represents an application object.

Return

This property returns a sheet object representing the active sheet in the current Excel Workbook. It returns null if the worksheet has no active sheet.

Read Data from ActiveSheet

One of the basic tasks in VBA’s day to day life is to read data from Excel ActiveSheet. The worksheet’s range object can be used to read data from the ActiveSheet Object.

Although, if you are referring to the topmost sheet, then it’s not necessary to specify ActiveSheet function before Range object.  In the below code Range (“B5”) can also read the data from ActiveSheet.

Code:

Sub ActiveSheet_ReadProperty()
 'reading the value of B5 cell in the ActiveSheet
 'with the help of Range object
 MsgBox ActiveSheet.Range("B5")
 'if ActiveCell object is not specified
 MsgBox Range("B5") 'it will return the same value as above
 End Sub

Let’s work with the set-by-step code of lines:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. Next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

VBA ActiveSheet

Step 3: In the Module window, introduce the sub-block, followed by your macro name.

VBA ActiveSheet

Step 4: We will call the ActiveSheet function and to read the data will specify the Range object (specifying the cell or range in it.

VBA ActiveSheet

Step 5: With the help of ‘Msgbox’ will display the output.

VBA ActiveSheet

Step 6: We will repeat the above 2 steps, but this time will not specify the ActiveSheet object.

VBA ActiveSheet

Output

Step 7: Execute the above code either by pressing the F5 shortcut key or by clicking on the Run button.

Step 8: You will notice that the output has been displayed in the message box.

VBA ActiveSheet

Step 9: Press OK. Again, the message box dialog will be displayed, and both the outputs will be the same.

VBA ActiveSheet

ActiveSheet Name

The VBA  ‘.Name ‘property of the ActiveSheet fetches the name of your sheet. With the help of the name, you can proceed with many applications.

Code:

Sub ActiveSheet_NameProperty()
 'fetching the name of the ActiveSheet with help of
 'ActiveSheet.Name property
 MsgBox "The name of ActiveSheet is" & ActiveSheet.Name
 End Sub

Let’s work with the set-by-step code of lines:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. Next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

VBA ActiveSheet

Step 3: In the Module window, introduce the sub-block, followed by your macro name.

VBA ActiveSheet

Step 4: With the help of MsgBox, we will display the name by using VBA ActiveSheet.Name property.

VBA ActiveSheet

Output

Step 5: Execute the above code either by pressing the F5 shortcut key or by clicking on the Run button.

Step 6: You will notice that the name has been displayed in the MsgBox.

VBA ActiveSheet

Password Protection in ActiveSheet

You can secure your ActiveSheet from unintended users and safeguard your data by using the VBA protect method. It will set a password in your current sheet. If needed, you can even remove it by using the VBA UnProtect method.

Syntax

  1. Protect Data
ActiveSheet.Protect ([Password], [DrawingObjects], …)
  • UnProtectData
ActiveSheet.UnProtect “password”

Code: 

Sub ActiveSheet_ProtectProperty()
 'protect method is used to secure the
 'ActiveSheet with password
 ActiveSheet.Protect "password", True, True
 End Sub

Let’s work with the set-by-step code of lines:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. Next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

VBA ActiveSheet

Step 3: Protect your Excel sheet with the help of ActiveSheet.Protect method.

VBA ActiveSheet

Output

Step 4: Run the output by clicking the F5 function key.

Step 5: you will notice you can no more write anything in the sheet. An alert dialogue box will pop up stating “The cell or chart you’re trying to change in on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password”

VBA ActiveSheet

To Unprotect the Sheet

To again access/ write or format the sheet you must unprotect the sheet. To unprotect the Excel sheet, use the ActiveSheet. Unprotect method.

Code:

Sub ActiveSheet_UnProtectProperty()
 'protect method is used to secure the
 'ActiveSheet with password
 ActiveSheet.Protect "password", True, True
 'unprotecting the sheet
 ActiveSheet.Unprotect "password"
 End Sub

Now your sheet has been unprotected.

ActiveSheet Clear Method

The ActiveSheet.Clear method is used to clear off all the content of the cells in the active cells.

Code:

Sub ActiveSheet_ClearProperty()
 'to clear the content of all the cells
 'in the Activesheet with the help of Clear method
 ActiveSheet.Cells.Clear
 End Sub
VBA ActiveSheet

Output

Before the Clear method

VBA ActiveSheet

After the Clear method: All the content would be erased.

VBA ActiveSheet

Activate another sheet as ActiveSheet

We can active any random sheet as an ActiveSheet. For this, the ‘.Activate’ method is used.

Code:

Sub ActiveSheet_ActivateSheet()
 'activating Sheet2
 Sheet2.Activate
 'inserting value in cell B2
 ActiveSheet.Cells(2, 2) = "Hello ActiveSheet"
 End Sub
VBA ActiveSheet

Output

You will notice that in Sheet2, at cell address B2, “Hello ActiveSheet” value has been entered.

VBA ActiveSheet

Related Topics

Excel VBA Cos Function

VBA Cos Function: The Cos function in VBA returns the cosine value for a supplied angle. Syntax Cos (Number) Parameter Number (required) –This parameter represents the number that you want the absolute value of. Return This function...

1 minute read.

Excel VBA CByte Function

VBA CByte Function: The CByte function in VBA converts an expression into a Byte data type. Syntax CByte (Expression) Parameter Expression (required) - This parameter represents the expression that that you want to convert to...

1 minute read.

Excel VBA For Each Loop

A For Each loop executes a statement or a group of statements for each element in an array or collection. It repeats the statement/condition/code for each element in a collection. For Each Loops loop through every...

4 minutes read.

VBA VBScript Regex Methods Regex

VBA VBScript Regex Methods Regex The VBA Regex supports 3 methods which are as follows: ExecuteReplaceText Execute The execute method is used to extract a match from the given based on the defined matching...

3 minutes read.

VBA Screen Updating

What is VBA Screen Updating property? Screen Updating is a VBA property which is used to display the output generation while running the code. If this property is enabled, we could see...

6 minutes read.

Miscellaneous Exercise of conditional statements and Loop

We have the already worked with syntax and examples of conditional statements and loops in the previous tutorials. In this tutorial, we will learn how to work with both together.  We will explain...

3 minutes read.

Excel VBA Tutorial

What is VBA? Introduction to Excel VBA: Visual Basic for Applications (VBA) is a programming language developed by Microsoft to automate operations in applications, such as Excel, Word, PowerPoint, etc. It...

5 minutes read.

Excel VBA MessageBox

Message Box The MsgBox in Excel VBA is a dialog box used to inform the users of your program by showing a custom message or get some necessary inputs such as Yes/No or...

4 minutes read.

Excel VBA IsError Function

VBA IsError Function: The IsError function in VBA returns a Boolean value showing whether the specified Expression represents an error or not. Syntax IsError (Expression) Parameter Expression (required)- This parameter represents the variant that you...

1 minute read.

UserForm and its Properties Excel VBA

UserForm and its Properties Userform has certain properties that can be viewed as category wise (based on appearance, behavior, font) or in an alphabetic manner. The property window is used to set or...

11 minutes read.

Excel VBA InStr Function

VBA InStr Function The InStr function in VBA searches for a substring inside the given string. It returns the position of a substring within a string, as an integer if the substring is found...

1 minute read.

Excel VBA Split Function

Excel VBA Split Function: The Split function in VBA is used to split a string into several substrings and return a one-dimensional array of substrings. Syntax Split (Expression, [Delimiter], [Limit], [Compare]) Parameter Expression (required) – This parameter...

2 minutes read.

Excel VBA Trim Function

The Trim function in VBA removes the leading and trailing spaces from the specified string. Syntax Trim (String) Parameter String (required) – This parameter represents the string from which you want to remove the leading and...

1 minute read.

Excel VBA User-Defined Functions

User-Defined Functions One of the advantages of VBA is that you can create your own functions using macros. These functions can be called and used as other functions in excel and use them. You can...

3 minutes read.

VBA Pivot Table

What is a Pivot Table? One of the most powerful features of Excel VBA is the Pivot Table. A pivot table is a VBA tool that is used to create summary...

3 minutes read.

Excel VBA Oct Function

VBA Oct Function: The Oct function in VBA converts the given number into octal notation and returns the result as a string. Syntax Oct (Number) Parameter Number (required) – This parameter represents the numeric value...

1 minute read.

VBA Subscript out of Range

What is Subscript out of Range? The VBA Subscript out of Range error (which is also called as Run-Time Error 9) mostly triggers when the user selects any cell, sheet, or workbook which does...

5 minutes read.

Excel VBA Minute Function

The Minute function in VBA returns the minute component for the specified time. Syntax Minute (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the minute component for the specified time. Example 1 Sub MinteFunction_Example1() ...

1 minute read.

Excel VBA IsNull Function

VBA IsNull Function: The IsNull function in VBA returns a Boolean, indicating whether a supplied expression is Null. Syntax IsNull (Expression) Parameter Expression (required)- This parameter represents the name of the argument that you want...

1 minute read.

Excel VBA InputBox

Input Box The InputBox function in VBA is used to prompt the users to enter values. The user can click either the OK button or can choose the CANCEL button. If the user clicks...

3 minutes read.