In one of the previous articles, we learned about cells and ranges. Today, we will learn to use Active Cell in VBA. We will learn to set the value of the Active Cell, get value from Active Cell, get row and column numbers corresponding to the active cell, and much more.
So let us start learning.
- What is ActiveCell Property in VBA?
- Syntax to use ActiveCell
- Examples to Use ActiveCell Property
What is ActiveCell Property in VBA?
Active Cell refers to the cell in edit mode if you start to type.
Here B2 is the active cell. In VBA, we have this ActiveCell property which represents the cell that is currently active at the moment. We can use this property to refer to the Active Cell in our VBA code. It has its own properties and methods associated with it.
For instance, this Sub Procedure when executed would change the value of the active cell in the currently active worksheet of the opened workbook to ExcelUnlocked.
Sub exChangeActiveCellValue() 'change the value of active cell ActiveCell.Value = "ExcelUnlocked" End Sub
Syntax to use ActiveCell
When we use the ActiveCell property, it automatically refers to the active cell in the active workbook. It does not matter how many workbooks were opened at that moment.
However, if you wish to access the contents of Active Cell in a specific worksheet of a workbook then we can use the collections.
Examples to Use ActiveCell Property
In this section of the article, we would implement some examples to learn to use ActiveCell Property.
Example 1 – Activate a Cell from Selected Range of Cells and Clear the Contents
We need to select a range using the Select Property of Range and then Activate a cell within that range using the Activate Property.
Enter the following code in a new module.
Sub exActivateCell() 'select the range A1:D4 Range("A1:D4").Select 'activate the cell B3 Range("B3").Activate 'clear the contents in active cell ActiveCell.Clear End Sub
When you run this Sub Procedure, it will select the range A1:D4 of Active Worksheet, the active cell would be B3 and then its contents will be cleared.
Note that this also clears the formatting from the active cell.
Example 2 – Get the Value of Active Cell
In this example, we would access the value of the active cell using the Value property and then print the contents in Active Cell using the Message Box.
Sub getActiveCellValue() 'declare a variant to store value in active cell Dim value 'store the value of active cell in variant value = ActiveCell.value 'print the variant MsgBox value End Sub
Run this sub procedure, it will print the value of active cell in a message box on the screen.
Example 3 – Set the Value of Active Cell to a Variable
We will set the value of the active cell to a range variable and then change the value of the variable to update the value in the active cell of a worksheet.
Sub exSetValueToVariable() 'declare a range object Dim myvariable As Range 'set the value of range variable to active cell Set myvariable = ActiveCell 'enter the value in range myvariable.value = "Hey!" 'change the interior color of range myvariable.Interior.Color = RGB(255,204,229) End Sub
When you run this Sub Procedure, it enters a text string “Hey!” to the active cell and also changes its interior color.
Example 4 – Active Cell with Offset
Using the Offset Property, we can refer to a cell that is a fixed number of rows and columns to the active cell.
For example, this would select the cell one row below and three columns to the right of the active cell.
This brings us to an end.
Thank you for reading.