Compile Error on Previously Working Code

[jhedges]

Team,

I adopted the following code from the forum and it has been working for years now, until recently in one workbook. The code also works fine in other workbooks. In the workbook in question I get a Compile Error Invalid number of Arguments or Invalid Property Assignment and it highlights the word "Format" colored blue in the code. Wondering if anyone could help me determine why I'm seeing this issue?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:="xxxx"
    On Error Resume Next
    Dim cmtText As String
    Dim Pos As Long
    Dim i As Integer
    Dim text As String
    Dim lArea As Long
    Dim TotalCommentLength As String
    Dim cmtTextLength As Long
    Dim NameLength As Long
    Dim StartPos As Long
    cmtText = InputBox("Please enter note(s):", "Note Text")
    If cmtText = "" Then Exit Sub
    NameLength = Len(Application.UserName)
    'include line feed at end of text to prevent formatt bleeding
    cmtText = [B][COLOR=#0000cd]format[/COLOR][/B](Now, "mm/dd/yy hh:mm:ss ampm") & "-" & Application.UserName & "  " & cmtText & Chr(10)
    cmtTextLength = Len(cmtText)
    If Target.Comment Is Nothing Then
        Target.AddComment text:=cmtText
        'Target.Comment.Visible = True
    Else
        Target.NoteText Chr(10) & cmtText, 99999
    End If
    'Auto size the comment area
    With Target.Comment.Shape
        .TextFrame.AutoSize = True
        If .Width > 350 Then
            lArea = .Width * .Height
            .TextFrame.AutoSize = False
            .Width = 350
            ' An adjustment factor of 0.8 seems to work ok.
            .Height = (lArea / 350) * 0.8
        End If
    End With
    'color the date and name text
    TotalCommentLength = Len(Target.Comment.text)
    StartPos = TotalCommentLength - cmtTextLength + 1
    Target.Comment.Shape.TextFrame.Characters(StartPos, 20).Font.ColorIndex = 41
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21, NameLength).Font.ColorIndex = 3
    'some of your code follows
    Dim Col As Integer, Row As Integer
    Row = Target.Row
    Col = Target.Column
    ' 'Call function to format cell
    If Not Target Is Nothing Then
        'FormatCellTemplateSheet Row, Col
    End If
    Cancel = True    'Remove this if you want to enter text in the cell after you add the comment
ActiveSheet.Protect Password:="xxxx", AllowFiltering:=True
End Sub

 

[footoo]

Try changing to :

cmtText = VBA.Format(Now, "mm/dd/yy hh:mm:ss ampm") & "-" & Application.UserName & " " & cmtText & Chr(10)

 

[Norie]

Has there been any code added in the workbook?

If so, anything named 'format'?

Have you checked under Tools>References... for anything marked as MISSING?

 

[jhedges]

Has there been any code added in the workbook?

If so, anything named 'format'?

Have you checked under Tools>References... for anything marked as MISSING?

Norie,

It is the first part. I had added a sub called format. I changed the name to something else and it started working again. It is always the little things...Thanks for your help and I appreciate the time.

 

[footoo]

Norie,

It is the first part. I had added a sub called format. I changed the name to something else and it started working again. It is always the little things...Thanks for your help and I appreciate the time.

In that case, it should also work if the code is changed to VBA.Format without having to change the name of the Format macro.

 

[jhedges]

In that case, it should also work if the code is changed to VBA.Format without having to change the name of the Format macro.

Footoo - Thanks for the information. It is always good to learn options and how things work.

Thanks