How to use (ByVal Target as Range) for multiple ranges throughout sheets - VBA?

Question

So I am trying to create a code that triggers a macro based on a event for multiple ranges, the ranges differ depending on the sheet so I planned on adding the Event code that would have different ranges in the Sheet Object tabs for each. The code I have worked great when it involved two different ranges. But when I add a third range it gives me the following error:

Below ill attach my module code and the (ByVal Target as Range) code I'm putting on each sheet.

    Private Sub Worksheet_Change(ByVal Target As range)
        Dim rngToCheck As range
        Set rngToCheck = Intersect(Target, Me.range("E5:E36", "P5:P36"))

        If rngToCheck Is Nothing Then Exit Sub

        On Error GoTo SafeExit
        Application.EnableEvents = False

        Dim rng As range
        For Each rng In rngToCheck
            Select Case rng.Value
                Case "Final Action Taken"
                    Final_Action_Taken
                Case "Populate Non Final Action Taken Date"
                    EnterNonFinal_Date
                Case "Populate Previous SPD Submission"
                    SPD_PreviousSubmission
                Case "Final Action Taken SPD"
                    Final_Action_TakenSPD
            End Select
         Next

    SafeExit:
        Application.EnableEvents = True
    End Sub

            Sub Final_Action_Taken()
        'Ask for the date of last submission of document by business


        If ActiveCell = "Final Action Taken" Then

        Dim myValue As Variant, Output As range


        myValue = InputBox("Please enter the final document submission date for the current Sign Off Year.", "Final Submission Date")

        Set Output = ActiveCell.Offset(0, 2)

        Output = myValue

        End If
        End Sub
Sub EnterNonFinal_Date()
If ActiveCell = "Populate Non Final Action Taken Date" Then

Dim Output As range
Dim myValue As Variant

myValue = "=today()"

Set Output = ActiveCell.Offset(0, 2)

Output = myValue

End If

End Sub

Sub SPD_PreviousSubmission()
'Ask for the date of last submission of document by business


If ActiveCell = "Populate Previous SPD Submission" Then

Dim myValue As Variant

Dim Output As range

Dim Output2 As range

Dim myValue2 As Variant

myValue = InputBox("Please enter the Previous SPD Submission Date.", "Previous SPD Submission Date")

Set Output = ActiveCell.Offset(0, 1)

Output = myValue

myValue2 = "=today()"

Set Output2 = ActiveCell.Offset(0, 2)

Output2 = myValue2

End If
End Sub

Sub Final_Action_TakenSPD()
'Ask for the date of last submission of document by business


If ActiveCell = "Final Action Taken SPD" Then

Dim myValue As Variant, Output As range


myValue = InputBox("Please enter the final document submission date for the current Sign Off Year.", "Final Submission Date")

Set Output = ActiveCell.Offset(0, 2)

Output = myValue

End If
End Sub

Please note I'm a beginner with VBA so I'm a bit lost, any help would be greatly appreciated.

Thank you in advance!

What line throws the error? Did you mean you tried something like Set rngToCheck = Intersect(Target, Me.range("E5:E36", "P5:P36", "Q5:Q36"))? — BigBen
Anything after the procedure call of Final_Action_Taken is going to throw an error since those procedures arent identified in this code snippet. Also, how are you defining the ActiveCell in the Final_Action_Taken procedure? — Zack E

BigBen BigBen · Accepted Answer · 2020-02-12T15:11:21

IIUC, you are looking to reference ranges like the following:

Set rngToCheck = Intersect(Target, Me.Range("E5:E36,P5:P36,Q5:Q36"))

From the Range documentation:

Use Range (cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells, to return a Range object

You're not specifying start and end cells, but rather the different areas of a range in question. So you want the range address all within quotes.

How to use (ByVal Target as Range) for multiple ranges throughout sheets - VBA?

2 Answers