VBA Active Cell

VBA Active Cell

Active cell means the specific cell which is active in the current active worksheet. For example, if in sheet 2 cell B4 is selected means the active cell is B4 in sheet 2. In VBA we use a reference of active cell to change the properties or values of the active cell. OR we use this function in certain situations when we need to make some changes in the active cell on some certain conditions which meet the requirements.

The active cell is a property in VBA. We use it in different situations in VBA. We can assign values to an active cell using VBA Active Cell function or fetch the address of the active cell. What did these functions return? Active cell Function returns the range property of the active cell in the active worksheet. As explained in the above statement in the definition if sheet 2 is active and cell B4 is active cell the active cell function in VBA will fetch the range properties of the cell B4 in sheet 2.

Syntax of Active Cell in Excel VBA

Below is the syntax of Active Cell in Excel VBA

The syntax is used to assign a certain value to the active cell.

Activecell.Value= “ “

The syntax will select the value or the property of the active cell in the active worksheet.

Application.Activecell

If we need to change the font of the active cell then the syntax will be as follows

Activecell.Font.(The font we want) = True

We can also display the rows and column of the active cell using the following syntax

Application.Activecell

Let us use the above syntax explained in a few examples and learn how to play with the active cells.

Examples of Excel VBA Active Cell

Below are the different examples of VBA Active Cell in Excel:

VBA Active Cell – Example #1

In this example, we want to change the value of the current cell with something cell. For example in sheet 1, select cell A2 and insert value as ANAND and we want to change the value for that active cell as ARAN.

Follow the below steps to use VBA Active Cell in Excel.

Step 1: Go to Developer’s tab and click on Visual Basic to open VB Editor.

Step 2: Click on Insert tab and click on modules to insert a new module.

Step 3: Declare a sub-function to start writing the code.

Code:

Sub Sample()

End Sub

Step 4: Activate the worksheet 1 by using the below function.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

Step 5: We can check that in cell A2 in sheet 1 we have the value as ANAND and it is the active cell.

Step 6: Now use the following statement to change the value of the active cell.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

ActiveCell.Value = "ARAN"

End Sub

Step 7: Run the above code from the run button provided or press F5.

We can see that the value in cell A2 has been changed.

VBA Active Cell – Example #2

Now we have changed the active cell Value from ANAND to ARAN. How do we display the current value of the active cell? This we will learn in this example.

Follow the below steps to use VBA Active Cell in Excel.

Step 1: Go to the developer’s Tab and click on Visual Basic to Open VB Editor.

Step 2: In the same module declare a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

Step 3: Activate the worksheet 1 by the following code.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate

End Sub

Step 4: Now let us select the active cell by the following code.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate
Set selectedCell = Application.ActiveCell

End Sub

Step 5: Now let us display the value of the selected cell by the following code.

Code:

Sub Sample1()

Worksheets("Sheet1").Activate
Set selectedCell = Application.ActiveCell
MsgBox selectedCell.Value

End Sub

Step 6: Run the above code by pressing F5 or by the run button provided and see the following result.

The active cell was A2 and it has the value as ARAN so the displayed property is ARAN.

VBA Active Cell – Example #3

Let us change the font of the cell A2 which was the selected cell. Let us make the font as BOLD. Initially, there was no font selected.

For this, Follow the below steps to use VBA Active Cell in Excel.

Step 1: Go to the Developer’s Tab and click on Visual Basic to open VB Editor.

Step 2: In the same module declare a sub-function to start writing the code.

Code:

Sub Sample2()

End Sub

Step 3: Let us activate the worksheet first in order to use the active cell.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

End Sub

Step 4: Let us change the font of the selected cell by the following code.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

ActiveCell.Font.Bold = True

End Sub

Step 5: Run the above code by pressing F5 or by the run button provided and see the result.

The font of the active cell is changed to BOLD.

VBA Active Cell – Example #4

Now we want to know what row or what column the currently active cell is in. How to do this is what we will learn in this example.

For this, Follow the below steps to use VBA Active Cell in Excel.

Step 1: Go to Developer’s Tab and click on Visual Basic to Open the VB Editor.

Step 2: In the same module declare a sub-function to start writing the code.

Code:

Sub Sample3()

End Sub

Step 3: Let us activate the worksheet first in order to use the active cell properties.

Code:

Sub Sample3()

Worksheets("Sheet1").Activate

End Sub

Step 4: Now we select the active cell by the following code.

Code:

Sub Sample3()

Worksheets("Sheet1").Activate
Set selectedCell = Application.ActiveCell

End Sub

Step 5: Now we can display the current row of the active cell by the following code.

Code:

Sub Sample3()

Worksheets("Sheet1").Activate
Set selectedCell = Application.ActiveCell
MsgBox selectedCell.Row

End Sub

Step 6: We can also get the current column of the active cell by the following code.

Code:

Sub Sample3()

Worksheets("Sheet1").Activate
Set selectedCell = Application.ActiveCell
MsgBox selectedCell.Row
MsgBox selectedCell.Column

End Sub

Step 7: Now press F5 or the run button provided to run the above code and see the following result.

The above result was the row of the active cell. Press ok to see the column of the active cell.

Things to Remember

There are few things which we need to remember about Active cell in VBA:

• The active cell is the currently active or selected cell in any worksheet.
• We can display or change the properties of the active cell address in VBA.
• In order to use the properties of the active cell, we must need to activate the current worksheet first.

Recommended Articles

This has been a guide to Excel VBA Active Cell. Here we discussed how to use VBA Active Cell property to assign value or fetch the address of the active cell in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Using VBA IFError in Excel
2. How to Use Excel Freeze Panes?
3. Tutorials on VBA Paste
4. Customize and Use Status Bar in Excel