VBA Selection

Selection is the property available with VBA. Once the range of cells is selected, we must decide what to do. Using this VBA “Selection” property, we can do everything we can with selected cells. One of the problems with the Selection property is we do not get to see the IntelliSense list. So, when writing the code, we must be sure of what we are doing without the IntelliSense list.

We look at the examples of selection in Excel VBA.

Example #1

Here is a simple example of a “selection” property with VBA. Of course, we want to first select the cells from A1 to B5 to write the VBA code like this.

Range (“A1:B5”).Select

Code:

Sub Selection_Example1()

  Range("A1:B5").Select

End Sub

This code will select the VBA range of cells from A1 to B5.

If we want to insert the value of “hello” to these cells, we can write the code like this.

Code:

Sub Selection_Example1()

  Range("A1:B5").Value = "hello"

End Sub

Similarly, once the cells are selected, it becomes “Selection.”

Code:

Sub Selection_Example1()

  Range("A1:B5").Select

  Selection.Value = "Hello"

End Sub

First, we have selected the range of cells from A1 to B5. So, this line will select the cells.

Once these cells are selected, we can refer to these cells by using the property “Selection” in Excel VBA. So, using the Selection property, we can insert the value of “Hello” to these cells.

It is the general overview of the “Selection” property in VBA.

Example #2

Now, we will see the VBA “Selection” property with variables. But, first, define the VBA variable as Range.

Code:

Sub Selection_Example2()

  Dim Rng As Range

End Sub

The range is an object variable since it is an object variable. Therefore, we need to set the range of cells using the “Set” keyword.

We will set the range as “Range(“A1:A6”).

Code:

Sub Selection_Example2()

  Dim Rng As Range
  Set Rng = Range("A1:A6")

End Sub

The variable “Rng” refers to the range of cells A1 to A6.

Now, we will write the code to insert the value of “Hello.”

Code:

Sub Selection_Example2()

  Dim Rng As Range
  Set Rng = Range("A1:A6")
  Rng.Value = "Hello"

End Sub

It will insert the value of “Hello” to the cells A1 to A6.

It does not matter where you are running the code. The active worksheet will insert the value “Hello” to cells A1 to A6.

But imagine the situation where you have to insert the word “Hello” wherever you select the cells with just a button click.

For this, we cannot set a specific range of cells. Rather we need to set the range as “Selection.”

Code:

Sub Selection_Example2()

  Dim Rng As Range
  Set Rng = Selection

End Sub

The variable “Rng” refers to the active cell or wherever we select the cells. For example, using this property (Selection) in Excel VBA, we can insert the value “Hello.”

Code:

Sub Selection_Example2()

  Dim Rng As Range
  Set Rng = Selection

  Selection.Value = "Hello"

End Sub

It will insert the word “Hello” into the cells of our selection. Now, we will select the cells from B2 to C6 and run the code. First, it will insert the “Hello” value.

Example #3

Now, we will see how we can change the interior color of the selected cells. Now, we want to change the interior color of the cells we will select. For this first, we have declared the variable as Range and set the range reference as “Selection.”

Code:

Sub Selection_Example3()

  Dim Rng As Range
  Set Rng = Selection

End Sub

Now, access the “Interior” property using the VBA Selection property.

Code:

Sub Selection_Example3()

  Dim Rng As Range
  Set Rng = Selection

  Selection.Interior

End Sub

Once the “Interior” property is selected, we need to decide what we need to do with this property. Since we need to change the color of the selected cell’s interior, select the property “Color.”

Code:

Sub Selection_Example3()

  Dim Rng As Range
  Set Rng = Selection

  Selection.Interior.Color

End Sub

Set the color property as “vbGreen.”

Code:

Sub Selection_Example3()

  Dim Rng As Range
  Set Rng = Selection
 
  Selection.Interior.Color = vbGreen

End Sub

So this will change the interior color of the selected cells to “vbGreen.”

Like this, we can use the Excel VBA “Selection” property in coding and do many more things easily.

Note: One of the biggest threats with the “Selection” property is we don’t get the IntelliSense list access while coding. As a new learner, it is almost impossible to remember all the properties and methods of the Selection property, so you need to be proficient in VBA to start using the “Selection” property.