Excel VBA Target Address refers to the location of a cell or range that caused a specific event to occur in an Excel worksheet. In this article, we automated profit calculation, automatically identify the mobile name under the budget and give you a MsgBox when production costs have changed.
How to Write Code in Selected Worksheet
In this section, we are going to create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.
- First, we go to the Developer option
- Then we will select Visual Basic.
Then a new window will pop up. Then we are going to follow these steps,
- First, we will go to select Microsoft Excel Objects.
- .Then select the relevant sheet to write your code..
What is the Target Address in VBA?
In Excel VBA, the Target Address refers to the address (or cell reference) of the cell that was changed when an event is triggered.
For example, in the Worksheet_Change event, the Target Address is the address of the cell that was changed by the user. This can be accessed through the Target parameter, which is passed to the event as a Range object.
Here’s an example of how you can use the Target Address in VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
MsgBox "You changed cell D6"
End If
End SubAfter writing this code in the corresponding private sub, we get the MsgBox when the target address cell value changes.
In this example, the macro checks if the Target Address is “$A$1”. If it is, a message box is displayed indicating that the user changed cell A1.
Note that the Target Address is always expressed in an absolute reference format, with the column letter and row number separated by a dollar sign ($).
Excel VBA Target Address: 3 Examples of Automating Worksheet
Here we are going to have an in-depth discussion of using Target Address in VBA to make your Excel automation easier. To do so, we have taken a data set of production and selling prices of different Mobiles like below.
1. Changing Cell Format According To Budget
In this article, we are going to automate to find Mobiles under My budget. Anytime you change the budget, you can see the cell format change and see the mobiles under your budget. We are going to use Target.Address to specify the cell which we are comparing every cell against.
- First, you have to go to Developer >> Visual Basics>> Microsoft Excel objects(Sheets, in this case).
- Then select the corresponding worksheet and write down the following code.
- You can copy the code from here.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Address = "$H$5" Then
For Each cell In Range("C5:C12")
If cell.Offset(0, 2).Value <= Target.Value Then
cell.Interior.Color = RGB(144, 238, 144)
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End If
End Sub- Finally, by changing the “My Budget to Buy Phone” value (in cell H5) we get the following result.
🔎Code Explanation
Private Sub Worksheet_Change(ByVal Target As Range)This line defines the event handler that will be triggered when a cell on the worksheet changes. Specifically, it is set up to respond to changes made to any cell on the worksheet.
Dim cell As RangeThis line declares a Range variable named “cell”. We will use it later in the code to loop through a range of cells
If Target.Address = "$H$5" ThenThis line sets up a condition that checks if the cell that was changed (as specified by the “Target” parameter) is cell H5. If the condition is true, the code within the “If” block will be executed.
For Each cell In Range("C5:C12")This line sets up a loop that will iterate through each cell in the range C5:C12. This range is hardcoded in the code, meaning that it will always check those cells regardless of which cell is changed
If cell.Offset(0, 2).Value <= Target.Value ThenThis line checks whether the value in the cell three columns to the right of the current “cell” (i.e. cell E5 if the current cell is C5) is less than or equal to the value of the cell that was changed (as specified by the “Target” parameter). If the condition is true, the code within the “If” block will be executed.
cell.Interior.Color = RGB(144, 238, 144)This line sets the interior color of the current “cell” to a shade of green specified by the RGB function. Specifically, the color is a light green with red, green, and blue values of 144, 238, and 144, respectively.
Else
cell.Interior.ColorIndex = xlNoneThis line is the “Else” block of the previous “If” statement. If the condition in the “If” statement is false, then this line will be executed, which sets the interior color of the current “cell” to none
End If
Next cell
End If
End Sub2. Automating Profit Calculation Using Target Address
In this article, we are going to automate profit calculation using Target Address. Anytime you change the Selling price, we will get the profit automatically without breaking any sweat/
- To do so we have written the following code in a private worksheet
- You can copy the code from here.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sellingPriceColumn As Range
Dim headerCell As Range
Dim profitColumn As Range
Set sellingPriceColumn = Range("E5:E12")
Set headerCell = Range("F4")
Set profitColumn = Range("F5:F12")
If Not Intersect(Range(Target.Address), Range(sellingPriceColumn.Address)) Is Nothing Then
headerCell.Value = "Profit" ' Update the header cell
headerCell.Font.Size = 12
headerCell.Font.Bold = True
profitColumn.FormulaR1C1 = "=RC[-1]-RC[-2]" ' Calculate the profit for each row
Range("B4:F12").Borders.LineStyle = xlContinuous
End If
End Sub- Finally, we get the result below.
🔎Code Explanation
Private Sub Worksheet_Change(ByVal Target As Range)This line defines the macro as a worksheet change event, which means it will run every time you make a change to the worksheet.
Dim sellingPriceColumn As Range
Dim headerCell As Range
Dim profitColumn As RangeThese lines declare three Range objects that we will use later in the code.
Set sellingPriceColumn = Range("E5:E12")
Set headerCell = Range("F4")
Set profitColumn = Range("F5:F12")These lines assign the range objects to specific cells on the worksheet.
If Not Intersect(Range(Target.Address), Range(sellingPriceColumn.Address)) Is Nothing ThenThis line checks if the target cell (i.e. the cell that you may have changed) intersects with the sellingPriceColumn range. If it does, it will execute the code inside the If statement.
headerCell.Value = "Profit" ' Update the header cell
headerCell.Font.Size = 12
headerCell.Font.Bold = TrueThese lines update the headerCell with the text “Profit” and format its font size and style.
profitColumn.FormulaR1C1 = "=RC[-1]-RC[-2]" ' Calculate the profit for each rowThis line sets the FormulaR1C1 property of the profitColumn range to “=RC[-1]-RC[-2]”, which calculates the profit for each row based on the selling price and cost.
Range("B4:F12").Borders.LineStyle = xlContinuousThis line applies a continuous border style to the range B4:F12, which includes the selling price, cost, and profit columns as well as the header row.
End If
End Sub3. Pop Up MsgBox When a Particular Cell Value of Target Address Changes in Excel
Here we have taken the entire column of Production Cost into Target Address using VBA in Excel. Any change in production cost will trigger the MsgBox.
- To do so you can write the following code in the corresponding private worksheet.
- To do so we have to write the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellRepresentative As String
Dim ProductionCost As Range
Set ProductionCost = Range("D5:D12") 'assuming production cost is in column D and data starts from row 5 to row 12
CellRepresentative = Target.Offset(0, -2).Value 'assuming cell representative is in column B
If Not Intersect(Target, ProductionCost) Is Nothing Then
If MsgBox("Warning: Production cost of " & CellRepresentative & " (" & Target.Address & ") has been changed!", vbOKOnly + vbExclamation, "Production Cost Change") = vbOK Then
'do nothing or add code to perform an action
End If
End If
End Sub- Finally, we get the output
🔎Code Explanation
Private Sub Worksheet_Change(ByVal Target As Range)This line defines the macro as a worksheet change event, which means it will run every time you make a change to the worksheet.
Dim CellRepresentative As String
Dim ProductionCost As RangeThese lines declare a string variable (CellRepresentative) and a range variable (ProductionCost) that we will use later in the code.
Set ProductionCost = Range("D5:D12") This line assigns the ProductionCost range object to the range of cells D5:D12, which contains the production cost data.
CellRepresentative = Target.Offset(0, -2).Value This line assigns the value of the cell that is two columns to the left of the target cell (i.e. the cell that you have changed) to the CellRepresentative variable. This assumes that the cell representative data is in column A.
If Not Intersect(Target, ProductionCost) Is Nothing ThenThis line checks if the target cell (i.e. the cell that you have changed) intersects with the ProductionCost range. If it does, it will execute the code inside the If statement.
If MsgBox("Warning: Production cost of " & CellRepresentative & " (" & Target.Address & ") has been changed!", vbOKOnly + vbExclamation, "Production Cost Change") = vbOK ThenThis line displays a message box that warns the user that you have changed the production cost for the cell representative. The message includes the CellRepresentative value and the address of the changed cell. The vbOKOnly + vbExclamation arguments specify that the message box should have an exclamation mark icon and an “OK” button. If the user clicks the “OK” button, it will execute the code inside the If statement.
'do nothing or add code to perform an action
End If
End If
End SubIntersect Method For Single Cell in Excel VBA
Here we are going to automate Profit Calculation like example 2 but the code is different from example 2. Here we are going to use Intersect Target rather than Intersect(Range(Target.Address), Range(sellingPriceColumn.Address)).
- First, you have to go to Developer >> Visual Basics>> Microsoft Excel objects.
- Then select the corresponding worksheet and write down the following code.
- You can copy the code from here.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sellingPriceColumn As Range
Dim headerCell As Range
Dim profitColumn As Range
Set sellingPriceColumn = Range("E5:E12")
Set headerCell = Range("F4")
Set profitColumn = Range("F5:F12")
If Not Intersect(Target, sellingPriceColumn) Is Nothing Then
headerCell.Value = "Profit" ' Update the header cell
headerCell.Font.Size = 12
headerCell.Font.Bold = True
profitColumn.FormulaR1C1 = "=RC[-1]-RC[-2]" ' Calculate the profit for each row
Range("B4:F12").Borders.LineStyle = xlContinuous
End If
End Sub- Finally, we get the output using Intersect target.
Preventing Event Loops in VBA
Now we are going to increase the selling price by one dollar recursively. But if you write the code below
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E5:E12")) Is Nothing Then
Range("H5").Value = Range("H5").Value + 1
End If
End SubIf, at each runtime, the worksheet_change event changes the content of a cell that itself is part of the Target Range (ie. which triggers the change event), it will result in reprocessing the change event repeatedly.
Recursion is the process of repeating in a similar way when the procedure calls itself. Refer to the above example of a recursive loop code, if any cell content in the range E5:E12 is changed by the user, the cell E5 value will change and increment by 1; this will again trigger the change event [because of the change in the value of cell E5 which is in the Target Range(“E5:E12”)] and will, in turn, change the cell E5 value by incrementing it by 1; and then this change in cell E5 will again trigger the change event and change the cell E5 value by incrementing it by 1; and so on. This will result in a recursive loop which might result in a ‘Out Of Stack Space’ untrappable error, or depending on the Excel setting, the loop might terminate at a threshold limit of say 100. To prevent this, enter the following at the beginning of the code.
- To handle this error we can write down the following code.
- You can copy the code from here.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next 'skip all run-time errors
If Target.Address = "$D$5" Or Target.Address = "$F$5" Then Exit Sub
Application.EnableEvents = False
If Target.Column = 4 Or Target.Column = 5 Then
If Target Then
Range("H5").Value = Range("H5").Value + 1
End If
End If
Application.EnableEvents = True
On Error GoTo 0 'Turn off error trapping and re-allow run time errors
End Sub- Finally, we get the output of how many numbers has changed the data without Out of Space Error.
🔎Code Explanation
Private Sub Worksheet_Change(ByVal Target As Range)Whenever a change occurs in the worksheet, it will trigger this macro. The macro takes the Target cell (the cell that you have changed) as a parameter.
On Error Resume Next 'skip all run-time errorsThis line turns on error handling, which means that if an error occurs during the execution of the macro, VBA will continue executing the code from the next line rather than stopping with an error message.
If Target.Address = "$D$5" Or Target.Address = "$F$5" Then Exit SubThis line checks if the Target cell is either D5 or F5. If it is, the macro exits without executing any further code.
Application.EnableEvents = FalseTurns off event handling, which means that any changes made to the worksheet during the execution of the macro will not trigger other macros or events.
If Target.Column = 4 Or Target.Column = 5 ThenThis line checks if the Target cell is in column D or E (columns 4 and 5, respectively).
If Target ThenThis line checks if the Target cell is not empty.
Range("H5").Value = Range("H5").Value + 1Increments the value of cell H5 by 1.
End If
End IfThese lines end the If statements that checked if the Target cell is in column D or E and if it is not empty.
Application.EnableEvents = True\This line turns on event handling again so that changes made to the worksheet after the execution of the macro will trigger other macros or events.
On Error GoTo 0 'Turn off error trapping and re-allow run time errorsThis line turns off error handling so that any errors that occur after this point will stop the execution of the macro and display an error message.
End SubFrequently Asked Questions (FAQs)
Q: Why is the VBA target address important?
A: The VBA target address is important because it allows the VBA code to identify and manipulate the selected cells, enabling the automation of repetitive tasks and increasing productivity.
Q: How do I access the target address in the VBA code?
A: You can access the target address in the VBA code by using the “Target” parameter, which is passed to the Worksheet_SelectionChange event. You can then use this parameter to determine the address of the selected cell or range of cells.
Q: How can I use the target address in the VBA code?
A: You can use the target address in VBA code to perform a wide range of tasks, such as changing the formatting of the selected cells, manipulating data in the selected cells, or triggering other events based on the user’s selection.
Q: Can I change the target address in the VBA code?
A: Yes, you can change the target address in the VBA code by using the “Range” object and specifying a new cell or range of cells. However, it is important to be careful when modifying the target address, as it can cause unintended consequences in your Excel workbook.
Q: What are some common mistakes to avoid when working with the target address in the VBA code?
A: Some common mistakes to avoid when working with the target address in VBA code include not checking for null values, not properly handling errors, and not fully understanding the scope of your code.
Things to Remember
- Target Address is a property of the Range object in VBA, and it represents the address of the cell that triggered a worksheet event.
- When working with worksheet events, you can use Target Address to determine which cell triggered the event and take appropriate actions based on that information.
- To access the value of the cell that triggered the event, you can use the Value property of the Range object, like this: Range(Target.Address).Value or Target.Value,
- It’s important to validate the Target Address to ensure that it refers to a valid cell in the worksheet. You can use the IsEmpty and IsNumeric functions to do this.
- If the Target Address refers to a range of cells, you can use the Intersect function to determine if the range intersects with a specific cell or range of cells.
- Keep in mind that the Target Address property is only available within worksheet event procedures, such as Worksheet_Change, Worksheet_SelectionChange, Worksheet_Activate, etc.
- Finally, be sure to test your code thoroughly to ensure that it works as expected and handles all possible scenarios that may arise.
Download Practice Workbook
You can download the Excel workbook that we used to prepare this article.
Conclusion
The Target Address is a tool in Excel VBA that helps developers know which cell or cells on a worksheet caused an event to occur. You can use this information to make the code more efficient and effective. To use the Target Address, developers need to make sure they validate the address, access the cell’s value, and use the Intersect function if necessary. By following these steps and testing the code carefully, developers can make sure their Excel applications work well.
