CODEWITHSUNDEEP
excelvbacompiler-errorsarguments
marsprogrammer
marsprogrammer

Reputation: 235

Excel Macro Compile Error - Argument not Optional

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

Upvotes: 0

Views: 72

Answers (2)

VBasic2008
VBasic2008

Reputation: 54853

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.

enter image description here

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

Upvotes: 1

Black cat
Black cat

Reputation: 6271

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

Upvotes: 1

Related Questions