Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 440
Default adding/appending comment by code

Hi,
The following code should:
1) add a comment with text & date when a date is entered in a cell in 'I'
2) append the above comment with new text and date when a date is entered in
'J'

The first part works fine, but the second doesn't and I can't figure out why!

Private Sub Worksheet_Change(ByVal Target As Range)

'adds a comment box to cells in column K (if an "Certificate of Service"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("I6:I30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 2).AddComment.Text Text:="AoS due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If

'appends the comment box in cells in column K if a "Particulars of Claim"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 1).EditComment.Text Text:="Due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If
End Sub


Any ideas?
Thanks
--
Traa Dy Liooar

Jock
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default adding/appending comment by code

You need to store the comment to a variable and delete and set the comment
again.

'date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
If Target.Offset(0, 1).Comment Is Nothing Then
Target.Offset(0, 1).AddComment.Text Text:="Due date: " &
Target.Value + 14
Else
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp & " Due date: " &
Target.Value + 14
End If

On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If



--
If this post helps click Yes
---------------
Jacob Skaria


"Jock" wrote:

Hi,
The following code should:
1) add a comment with text & date when a date is entered in a cell in 'I'
2) append the above comment with new text and date when a date is entered in
'J'

The first part works fine, but the second doesn't and I can't figure out why!

Private Sub Worksheet_Change(ByVal Target As Range)

'adds a comment box to cells in column K (if an "Certificate of Service"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("I6:I30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 2).AddComment.Text Text:="AoS due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If

'appends the comment box in cells in column K if a "Particulars of Claim"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 1).EditComment.Text Text:="Due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If
End Sub


Any ideas?
Thanks
--
Traa Dy Liooar

Jock

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default adding/appending comment by code

Can also do it without deleting the comment

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
Dim v
On Error GoTo errExit
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target(1)
v = .Value
If IsDate(v) Then
s = "Due date: " & v + 14
If .Offset(0, 1).Comment Is Nothing Then
.Offset(0, 1).AddComment.Text Text:=s
Else
With .Offset(0, 1).Comment
s = vbLf & s
.Text Text:=.Text = s
End With
End If
End If
End With
End If

errExit:
End Sub

If you want to preserve existing formatting change
.Text Text:=.Text = s
to
.Shape.TextFrame.Characters(Len(.Text), Len(s)).Text = s

No need to disable events for this

Regards,
Peter T


"Jock" wrote in message
...
Hi,
The following code should:
1) add a comment with text & date when a date is entered in a cell in 'I'
2) append the above comment with new text and date when a date is entered
in
'J'

The first part works fine, but the second doesn't and I can't figure out
why!

Private Sub Worksheet_Change(ByVal Target As Range)

'adds a comment box to cells in column K (if an "Certificate of Service"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("I6:I30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 2).AddComment.Text Text:="AoS due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If

'appends the comment box in cells in column K if a "Particulars of Claim"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 1).EditComment.Text Text:="Due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If
End Sub


Any ideas?
Thanks
--
Traa Dy Liooar

Jock



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 440
Default adding/appending comment by code

Great stuff.
When the comment is set again, can it be formatted with strikethrough to
make it obvious it's not the most recent addition?
Thanks
--
Traa Dy Liooar

Jock


"Jacob Skaria" wrote:

You need to store the comment to a variable and delete and set the comment
again.

'date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
If Target.Offset(0, 1).Comment Is Nothing Then
Target.Offset(0, 1).AddComment.Text Text:="Due date: " &
Target.Value + 14
Else
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp & " Due date: " &
Target.Value + 14
End If

On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If



--
If this post helps click Yes
---------------
Jacob Skaria


"Jock" wrote:

Hi,
The following code should:
1) add a comment with text & date when a date is entered in a cell in 'I'
2) append the above comment with new text and date when a date is entered in
'J'

The first part works fine, but the second doesn't and I can't figure out why!

Private Sub Worksheet_Change(ByVal Target As Range)

'adds a comment box to cells in column K (if an "Certificate of Service"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("I6:I30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 2).AddComment.Text Text:="AoS due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If

'appends the comment box in cells in column K if a "Particulars of Claim"
date is entered
On Error Resume Next
If Not Intersect(Target, Me.Range("J5:J30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
Target.Offset(0, 1).EditComment.Text Text:="Due date: " &
Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If
End Sub


Any ideas?
Thanks
--
Traa Dy Liooar

Jock

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a note/comment edju Excel Discussion (Misc queries) 3 March 28th 07 01:14 PM
Adding a comment to a cell mancitmis New Users to Excel 2 November 3rd 05 09:52 AM
Adding a new comment with a function? Cheese Excel Discussion (Misc queries) 4 July 31st 05 11:46 AM
Adding Comment and AutoSizing Troy[_3_] Excel Programming 0 May 27th 04 01:06 AM
Adding Comment to cell Troy H Excel Programming 5 May 14th 04 09:31 AM


All times are GMT +1. The time now is 02:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"