What is Excel VBA Selection Property?
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.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Selection (wallstreetmojo.com)
Examples of Selection Property in VBA
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 codeWrite The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more like this.
Range (“A1:B5”).Select
Code:
Sub Selection_Example1()
Range(“A1:B5”).Select
End Sub
This code will select the VBA range of cellsVBA Range Of CellsThe selection range is the desired area by the user to put effects, add data, or make changes in those specific cells only. The range is defined by formulas by the addresses of two cells separated by a colon. The cell where the selection range begins is still active.read more from A1 to B5.
If we want to insert the value of “hello” to these cells, we can write the code like this.
Sub Selection_Example1()
Range(“A1:B5”).Value = “hello”
End Sub
Similarly, once the cells are selected, it becomes “Selection.”
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 RangeVBA Variable As RangeRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more.
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”).
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.”
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.”
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.”
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.”
Sub Selection_Example3()
Dim Rng As Range Set Rng = Selection
End Sub
Now, access the “Interior” property using the VBA Selection property.
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.”
Sub Selection_Example3()
Dim Rng As Range Set Rng = Selection
Selection.Interior.Color
End Sub
Set the color property as “vbGreen.”
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.
Recommended Articles
This article is a guide to VBA Selection. Here, we discussed what Selection Property is in Excel VBA, with some practical examples and a download template. Below are some useful Excel articles related to VBA: –
- VBA Text Function ExamplesVBA OperatorsClose UserForm in VBA
