I’m getting the error “Argument not optional” when trying to call a macro on a worksheet change. What I’m trying to do is anytime something is changed in column A then run macro1, anytime something is changed in column E then run macro2, and anytime something is changed in column H then run macro3. Here is the code I’m using:
Private Sub Worksheet_Change(ByVal target As Range)
Dim rng As Range
Dim cell As Range
' check for change in columns A, E, and H
Set rng = Intersect(target, Me.Range("A:A, E:E, H:H"))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
Select Case cell.Column
Case 1
' Column A changed, run macro1
Call macro1
Case 2
' Column E changed, run macro2
Call macro2
Case 3
' Column H changed, run macro3
Call macro3
End Select
Next cell
Application.EnableEvents = True ' Enable events again
End If
End Sub
Unfortunately, whenever I try to compile or run the macro I get the error “Argument not optional” and it highlights the line Call macro1. Any help I can get would be great. I’m new to VBA so I’m sure it is something dumb on my part.
Gary Bettencourt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1