Excel Macro Compile Error - Argument not Optional

Question

I am getting an error message when I run my vba code in excel. The error message is a compile error with a message "Argument not Optional". I do not know where I am going wrong in the code.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

If Not Intersect(Target, Union(Range("K15:K31"))) Is Nothing Then
If Target.Interior.ColorIndex = 43 Then
Target.Interior.ColorIndex = 0
ActiveCell.Value = ""
Else: Target.Interior.ColorIndex = 43
ActiveCell.Value = ChrW(&H2713)
ActiveCell.Font.ColorIndex = 2
End If
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Set Cancel = True

If Not Intersect(Target, Union(Range("K15:K31"))) Is Nothing Then
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = 48
ActiveCell.Value = "N/A"
ElseIf Target.Interior.ColorIndex = 48 Then
Target.Interior.ColorIndex = 0
Else: Target.Interior.ColorIndex = 3
ActiveCell.Value = ChrW(&H2716)
ActiveCell.Font.ColorIndex = 2
End If
End If
End Sub

Union() should have two arguments. I don't think you need to use it. Try If Not Intersect(Target, Me.Range("K15:K31")) Is Nothing Then — taller
– taller, Commented Apr 25, 2024 at 5:28

Black cat · Accepted Answer · 2024-04-25 05:29:06Z

1

The Union method requires two arguments. In your code to use this method is unneccessary. Simply delete it and use only the Range object.

If Not Intersect(Target, Range("K15:K31")) Is Nothing Then

answered Apr 25, 2024 at 5:29

Black cat

7,6358 gold badges69 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2024-04-25 07:02:33Z

Worksheet Events

Black cat's answer resolves your immediate issue.
Here are just some improvements (tips).
- You could use .Color (the out-commented values) instead of .ColorIndex to always have the same colors regardless of the color scheme. You'll have to use .ColorIndex = 0 for no formatting though.
- You have changed the font color in the Else statements but you're not changing it 'back' in the other cases. Not sure if that's an issue.
- In both cases, the Select Case statement looks more readable than the If statement.
- In both cases, Cancel = True means that you won't be able to edit the cell so use it only for cells of the given range.
- Regarding the 2nd code (Set Cancel = True), the Set keyword is used only for objects (Workbook, Worksheet, Range...) so you cannot use it with a Boolean.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Intersect(Me.Range("K15:K31"), Target) Is Nothing Then Exit Sub
    
    Cancel = True
    
    With Target
        Select Case .Interior.ColorIndex
            Case 43
                .Interior.ColorIndex = 0
                .Value = Empty
            Case Else
                .Interior.ColorIndex = 43 ' 52377
                .Font.ColorIndex = 2 ' 16777215
                .Value = ChrW(&H2713)
        End Select
    End With

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    If Intersect(Me.Range("K15:K31"), Target) Is Nothing Then Exit Sub

    Cancel = True

    With Target
        Select Case .Interior.ColorIndex
            Case 3
                .Interior.ColorIndex = 48 ' 9868950
                .Value = "N/A"
            Case 48
                .Interior.ColorIndex = 0
            Case Else
                .Interior.ColorIndex = 3 ' 255
                .Font.ColorIndex = 2 ' 16777215
                .Value = ChrW(&H2716)
        End Select
    End With

End Sub

Collectives™ on Stack Overflow

Excel Macro Compile Error - Argument not Optional

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related