VBA Selection

What is Excel VBA Selection Property?

Selection is the property available with VBA. Once the range of cells selected we need to decide upon what we need to do. So, using this VBA “Selection” property we can do all the things we can do with selected cells. One of the problems with Selection property is we don’t get to see the IntelliSense list. So when we are writing the code we need to absolutely sure of what we are doing without the IntelliSense list.

Examples of Selection Property in VBA

Here we look at the examples of selection in Excel VBA.

Example #1

Let me show you a simple example of “selection” property with VBA. Now I want to first select the cells from A1 to B5, for that we can write the VBA code like this.

Range (“A1:B5”).Select

Code:

Sub Selection_Example1()

  Range("A1:B5").Select

End Sub

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

If I want to insert the value of “hello” to these cells I 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

In the above first, I 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 Selection property we can insert the value of “Hello” to these cells.

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

Example #2

Now we will see the VBA “Selection” property with variables. 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 we need to set the range of cells using the “Set” keyword.

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

Code:

Sub Selection_Example2()

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

End Sub

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

Now I 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

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

It doesn’t matter from where you are running the code, in the active worksheet it will insert the value “Hello” to the cell A1 to A6.

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

For this we cannot set the 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

Now the variable “Rng” refers to the active cell or wherever we select the cells. Now using this property (Selection) in excel VBA we can insert value “Hello”.

Code:

Sub Selection_Example2()

  Dim Rng As Range
  Set Rng = Selection

  Selection.Value = "Hello"

End Sub

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

Example #3

Now we will see how we can change the interior color of the selected cells. Now I want to change the interior color of the cells I will select. For this first, I 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 Using VBA Selection property access “Interior” property.

Code:

Sub Selection_Example3()

  Dim Rng As Range
  Set Rng = Selection

  Selection.Interior

End Sub

Once the “Interior” property 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 Excel VBA “Selection” property in coding and do lot more things easily.

Note: One of the biggest threats with “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 Selection property, so you need to be absolutely pro in VBA to start using “Selection” property.

Recommended Articles

This has been a Guide to VBA Selection. Here we discussed what is Selection Property in Excel VBA along with some practical examples and a download template. Below are some useful excel articles related to VBA –

• Comment VBA Block using Toolbar
• Examples of Intersect in VBA Excel
• Examples  to Change Directory using VBA
• VBA Text Function Examples
• Rename Excel Sheet using VBA
• VBA Wait Function
• GetOpenFilename Method in VBA
• VBA Free Course
• VBA Comparison Operators
• Close UserForm in VBA