CODEWITHSUNDEEP
vbaexcel
Artur Rutkowski
Artur Rutkowski

Reputation: 537

Compile error: Argument not optional

The below code lies in one of the sheets - I mean that it is not in a module, but in Sheet1 which is "Microsoft Excel Object".
I keep getting the following error:

Compile error: Argument not optional

It seems that I cannot pass any argument out of the Private Sub Worksheet_BeforeRightClick.

Could you help me out somehow?
The code below: 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo forward
    'checkinh if column A or B are non-empty (so that the code won\t work below the table:
    If Cells(Target.Row, 1).Value <> 0 Or Cells(Target.Row, 2).Value <> 0 Then
        'only column F and G, also exclude firs row (headings):
        If Target.Value = 0 And Target.Column > 5 And Target.Column < 8 And Target.Row > 1 And Target.Interior.Pattern = xlNone Then
            If Target.Column = 6 And Cells(Target.Row, 7).Value = UCase(Left(Environ("username"), 2)) Then
                r = Target.Row
                TickTock
            End If
            Cancel = True
            Target = UCase(Left(Environ("username"), 2))
            'm doesn't add a time
            If Target.Column = 7 Then
                Sheets(1).Cells(Target.Row, Target.Column - 2).Value = Format(Now - (61 / 1440), "hh:mm")
            End If
            'if there is no c then m adds a time
            If Target.Column = 6 And Cells(Target.Row, Target.Column + 1).Interior.Pattern <> xlNone Then
                Sheets(1).Cells(Target.Row, Target.Column - 1).Value = Format(Now - (61 / 1440), "hh:mm")
            End If
        End If
    End If

    Exit Sub
forward:
    Exit Sub

End Sub

Private Sub TickTock(r As Integer)
    Cells(r, 6).Interior.Color = 65535
End Sub

Upvotes: 0

Views: 539

Answers (1)

Tim Williams
Tim Williams

Reputation: 166850

The TickTock Sub has no optional arguments, but in your event handler you're calling it with no argument.

Maybe you need to be using:

TickTock r

Upvotes: 1

Related Questions