Run Macro on click of cell in range target - Intersect/Target Clarification

[Prevost]

Hi There,

I have a question regarding the code below which is written on a worksheet. Firstly, does all code written on a worksheet have to be named "Worksheet_SelectionChange"? And what is "ByVal Target" mean? Lastly, the line "If Not Intersect(Target, CheckRange) Is Nothing Then" is not quite clear to me. Is this a double negative? I would have thought that if the cell intersect was selected I could just write "If Intersect(Target, CheckRange) Then". However, this does not seem to work. What is going on? This would help me understand the code so that I know what to do in the future so if anyone has any insight or clarifications that would be greatly appreciated!

Thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim CheckRange As Range
    Set CheckRange = Range(Cells(6, 9), Cells(500, 9))
    If Not Intersect(Target, CheckRange) Is Nothing Then 
        Message = MsgBox("Would you lke to enter this date into all date cells?", vbYesNo, "Enter Date")
            If Message = vbYes Then
                i = ActiveCell.Row
                Call EnterDateMultipleCells
                Cells(i, 11) = Cells(i, 9)
                Cells(i, 15) = Cells(i, 9)
                Cells(i, 17) = Cells(i, 9)
                Cells(i, 21) = Cells(i, 9)
                Cells(i, 23) = Cells(i, 9)
                Cells(i, 27) = Cells(i, 9)
                Cells(i, 29) = Cells(i, 9)
            ElseIf Message = vbNo Then
                Call EnterDateMultipleCells
            End If
    End If
End Sub

 

[Jerry Sullivan]

Firstly, does all code written on a worksheet have to be named "Worksheet_SelectionChange"?

Hi Prevost, Worksheet_SelectionChange is one of many Worksheet events. You can see a list of available events by clicking on the worksheet's tab > View Code...which opens up the VB Editor. At the top of the sheet code window you'll see two dropdowns displaying (General) and (Declarations). From the left dropdown, select Worksheet, then the Right dropdown will show all the worksheet events that are available.

And what is "ByVal Target" mean?

Target is a Range Object that represents the Range that triggered the event. For the Worksheet_SelectionChange event, the Target is the Cell or Range of Cells selected by the user. Target is an argument passed to the procedure so it can be used in your code. A simple example would be

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Msgbox "You selected range: " & Target.Adddress
End Sub

Arguments are passed to procedures either ByVal or ByRef.
When passed ByRef, a change made to the variable will also affect the variable in the calling code, whereas changes to variables passed ByVal will not.

A key point to understand is that for Worksheet Event code, you should use the declarations that VBA creates automatically when you click on the event name dropdowns instead of trying to write your own, or modify those.

An example declaration is:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

You can't modify that to make Target a Range or add other arguments.

Lastly, the line "If Not Intersect(Target, CheckRange) Is Nothing Then" is not quite clear to me. Is this a double negative? I would have thought that if the cell intersect was selected I could just write "If Intersect(Target, CheckRange) Then". However, this does not seem to work. What is going on?

The reason that doesn't work is that the expression Is Nothing isn't the same as = False.

These two expressions are equivalent...

If Not ((Target.Value="X") = False) Then

If Target.Value="X" Then

The "Is Nothing" expression in your code example is used to test whether or not the expression "Intersect(Target, CheckRange)" evaluates to a valid range object.