Compile Error on Previously Working Code

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
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?

Code:
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try changing to :

cmtText = VBA.Format(Now, "mm/dd/yy hh:mm:ss ampm") & "-" & Application.UserName & " " & cmtText & Chr(10)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top