I have found the following code online and it adds a comment to a cell if
someone changes it. It works fine in the free download and if I paste the
code into a blank workbook. But when I put it into a specific worksheet I
have the code breaks on the format date function. Any help would be great.

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
& "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

Tim Peter

Hi,

There's nothing wring with the code, are you sure your not suffering from an
unfortunate line-wrap. The following bit of the code goes all in a single line

Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
& "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
Environ("UserName")


or you can use continuation like this and break it in 3 lines

Target.AddComment.Text Text:="Previous Value was " & _
preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & _
Chr(10) & "By " & Environ("UserName")

Mike

"tpeter" wrote:

> I have found the following code online and it adds a comment to a cell if
> someone changes it. It works fine in the free download and if I paste the
> code into a blank workbook. But when I put it into a specific worksheet I
> have the code breaks on the format date function. Any help would be great.
>
> Option Explicit
> Public preValue As Variant
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> Target.ClearComments
> Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> Environ("UserName")
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> If Target = "" Then
> preValue = "a blank"
> Else: preValue = Target.Value
> End If
> End Sub
>
> Tim Peter

Thanks for answering Mike,

That is the part I can't figure out. It works in blank workbooks but when I
put it into a current workbook I have created it breaks on format and says
"expected variable or procedure, not modual". If I take out the Format then
it works but I have no date when the cell was changed. Thanks again for your
help.

Tim Peter

"Mike H" wrote:

> Hi,
>
> There's nothing wring with the code, are you sure your not suffering from an
> unfortunate line-wrap. The following bit of the code goes all in a single line
>
> Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> Environ("UserName")
>
>
> or you can use continuation like this and break it in 3 lines
>
> Target.AddComment.Text Text:="Previous Value was " & _
> preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & _
> Chr(10) & "By " & Environ("UserName")
>
> Mike
>
> "tpeter" wrote:
>
> > I have found the following code online and it adds a comment to a cell if
> > someone changes it. It works fine in the free download and if I paste the
> > code into a blank workbook. But when I put it into a specific worksheet I
> > have the code breaks on the format date function. Any help would be great.
> >
> > Option Explicit
> > Public preValue As Variant
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> > Target.ClearComments
> > Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> > & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> > Environ("UserName")
> > End Sub
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> > If Target = "" Then
> > preValue = "a blank"
> > Else: preValue = Target.Value
> > End If
> > End Sub
> >
> > Tim Peter

Have you named your module the same as the code

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"tpeter" <(E-Mail Removed)> wrote in message
news:EBDC7329-1CC7-4E99-8463-(E-Mail Removed)...
> Thanks for answering Mike,
>
> That is the part I can't figure out. It works in blank workbooks but when
> I
> put it into a current workbook I have created it breaks on format and says
> "expected variable or procedure, not modual". If I take out the Format
> then
> it works but I have no date when the cell was changed. Thanks again for
> your
> help.
>
> Tim Peter
>
> "Mike H" wrote:
>
>> Hi,
>>
>> There's nothing wring with the code, are you sure your not suffering from
>> an
>> unfortunate line-wrap. The following bit of the code goes all in a single
>> line
>>
>> Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
>> & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
>> Environ("UserName")
>>
>>
>> or you can use continuation like this and break it in 3 lines
>>
>> Target.AddComment.Text Text:="Previous Value was " & _
>> preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & _
>> Chr(10) & "By " & Environ("UserName")
>>
>> Mike
>>
>> "tpeter" wrote:
>>
>> > I have found the following code online and it adds a comment to a cell
>> > if
>> > someone changes it. It works fine in the free download and if I paste
>> > the
>> > code into a blank workbook. But when I put it into a specific worksheet
>> > I
>> > have the code breaks on the format date function. Any help would be
>> > great.
>> >
>> > Option Explicit
>> > Public preValue As Variant
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > If Target.Count > 1 Then Exit Sub
>> > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
>> > Target.ClearComments
>> > Target.AddComment.Text Text:="Previous Value was " & preValue &
>> > Chr(10)
>> > & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
>> > Environ("UserName")
>> > End Sub
>> >
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > If Target.Count > 1 Then Exit Sub
>> > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
>> > If Target = "" Then
>> > preValue = "a blank"
>> > Else: preValue = Target.Value
>> > End If
>> > End Sub
>> >
>> > Tim Peter

Just to add to Don's idea...

Check to see if you created your own function/sub named Format.

tpeter wrote:
>
> Thanks for answering Mike,
>
> That is the part I can't figure out. It works in blank workbooks but when I
> put it into a current workbook I have created it breaks on format and says
> "expected variable or procedure, not modual". If I take out the Format then
> it works but I have no date when the cell was changed. Thanks again for your
> help.
>
> Tim Peter
>
> "Mike H" wrote:
>
> > Hi,
> >
> > There's nothing wring with the code, are you sure your not suffering from an
> > unfortunate line-wrap. The following bit of the code goes all in a single line
> >
> > Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> > & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> > Environ("UserName")
> >
> >
> > or you can use continuation like this and break it in 3 lines
> >
> > Target.AddComment.Text Text:="Previous Value was " & _
> > preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & _
> > Chr(10) & "By " & Environ("UserName")
> >
> > Mike
> >
> > "tpeter" wrote:
> >
> > > I have found the following code online and it adds a comment to a cell if
> > > someone changes it. It works fine in the free download and if I paste the
> > > code into a blank workbook. But when I put it into a specific worksheet I
> > > have the code breaks on the format date function. Any help would be great.
> > >
> > > Option Explicit
> > > Public preValue As Variant
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Count > 1 Then Exit Sub
> > > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> > > Target.ClearComments
> > > Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> > > & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> > > Environ("UserName")
> > > End Sub
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > If Target.Count > 1 Then Exit Sub
> > > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> > > If Target = "" Then
> > > preValue = "a blank"
> > > Else: preValue = Target.Value
> > > End If
> > > End Sub
> > >
> > > Tim Peter

--

Dave Peterson

That was it. I had a different modual nameded format. Thank all of you for
your excellent troubleshooting skills. I greatly appreciate it.

Tim Peter

"Dave Peterson" wrote:

> Just to add to Don's idea...
>
> Check to see if you created your own function/sub named Format.
>
> tpeter wrote:
> >
> > Thanks for answering Mike,
> >
> > That is the part I can't figure out. It works in blank workbooks but when I
> > put it into a current workbook I have created it breaks on format and says
> > "expected variable or procedure, not modual". If I take out the Format then
> > it works but I have no date when the cell was changed. Thanks again for your
> > help.
> >
> > Tim Peter
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > There's nothing wring with the code, are you sure your not suffering from an
> > > unfortunate line-wrap. The following bit of the code goes all in a single line
> > >
> > > Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> > > & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> > > Environ("UserName")
> > >
> > >
> > > or you can use continuation like this and break it in 3 lines
> > >
> > > Target.AddComment.Text Text:="Previous Value was " & _
> > > preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & _
> > > Chr(10) & "By " & Environ("UserName")
> > >
> > > Mike
> > >
> > > "tpeter" wrote:
> > >
> > > > I have found the following code online and it adds a comment to a cell if
> > > > someone changes it. It works fine in the free download and if I paste the
> > > > code into a blank workbook. But when I put it into a specific worksheet I
> > > > have the code breaks on the format date function. Any help would be great.
> > > >
> > > > Option Explicit
> > > > Public preValue As Variant
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.Count > 1 Then Exit Sub
> > > > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> > > > Target.ClearComments
> > > > Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
> > > > & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
> > > > Environ("UserName")
> > > > End Sub
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > If Target.Count > 1 Then Exit Sub
> > > > If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
> > > > If Target = "" Then
> > > > preValue = "a blank"
> > > > Else: preValue = Target.Value
> > > > End If
> > > > End Sub
> > > >
> > > > Tim Peter
>
> --
>
> Dave Peterson
> .
>