Inserting Comments with VBA

[akemeny]

Hi

I'm looking for a macro that will automatically enter the current date and a
note in the comments box when certain information is entered into specific
cells on my spreadsheets without deleting any old comments.

For example:

When V = unfavorable, automatic comment should enter:
- The current date (Blue and Bold)
- The note (standard font and color)
When V = favorable, automatic comment should enter:
- The current date (Blue and Bold
- The note (standard font and color)
Etc.

Is this possible??

 

[Don Guillett]

Try this idea.

Sub AddMOREtocomment()
With Range("P4")
.Comment.Text Text:=.Comment.Text & Chr(10) & "more"
End With
End Sub

 

[akemeny]

That does work, but I need it a bit more complex than that. For instance:

- Look at a6:a345 to find any cells containing the word RAC
- When the cell contents change to RAC insert a note in the comments stating
"(Current Date) Advised RAC Process of MN decision"

Then a Second Macro

- Look ab6:ab345 for any cells containing the word Upheld
- When the cell contents change to FI insert a note in the comments stating
"(Current date) Advised RAC Process of FI upheld decision"

Etc.

But I need the comment section to keep all the previous notes and enter the
most recent note on top rather than at the bottom.

Is all of this possible??

 

[Don Guillett]

It may be better if you send your workbook to me along with a snippet of
this email on a new sheet. I will be able to take a look later.

 

[sbitaxi]

Hi

I'm looking for a macro that will automatically enter the current date and a
note in the comments box when certain information is entered into specific
cells on my spreadsheets without deleting any old comments.

For example:

When V = unfavorable, automatic comment should enter:
        - The current date (Blue and Bold)
        - The note (standard font and color)
When V = favorable, automatic comment should enter:
        - The current date (Blue and Bold
        - The note (standard font and color)
Etc.

Is this possible??

Hello:

This will insert the comment, I can't get text formatting to work in a
comments field. There is probably a way, but this will at least start
you off.

Insert this into the Worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim MyCell As Range

MyString = Now
MyString2 = ": Unfavourable text"
MyString3 = ": Favourable text"


With Target
Select Case .Value
Case "favourable"
With .AddComment
.Text Now & MyString3
End With
Case "unfavourable"
With .AddComment
.Text Now & MyString2
End With
Case Else
'Do nothing
End Select
End With
End Sub

 

[Don Guillett]

Code sent to OP

 

[sbitaxi]

Thanks Don, my reply took over an hour to post for some reason.


Steven

 

[Don Guillett]

It could have been more condensed but left it this way for ease of
undertanding.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 6 Then Exit Sub
Application.EnableEvents = False

If Target.Column = 31 Then
With Cells(Target.Row, "a")
.Comment.Text Text:=Date & Chr(10) & "Advised FH RAC Process of RAC " _
& Target & " decision" & Chr(10) & .Comment.Text
End With
End If

If Target.Column = 39 Then
With Cells(Target.Row, "a")
.Comment.Text Text:=Date & Chr(10) & "Advised FH RAC Process of FI " _
& Target & " decision" & Chr(10) & .Comment.Text
End With
End If

Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi

I'm looking for a macro that will automatically enter the current date and
a
note in the comments box when certain information is entered into specific
cells on my spreadsheets without deleting any old comments.

For example:

When V = unfavorable, automatic comment should enter:
- The current date (Blue and Bold)
- The note (standard font and color)
When V = favorable, automatic comment should enter:
- The current date (Blue and Bold
- The note (standard font and color)
Etc.

Is this possible??

Hello:

This will insert the comment, I can't get text formatting to work in a
comments field. There is probably a way, but this will at least start
you off.

Insert this into the Worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim MyCell As Range

MyString = Now
MyString2 = ": Unfavourable text"
MyString3 = ": Favourable text"


With Target
Select Case .Value
Case "favourable"
With .AddComment
.Text Now & MyString3
End With
Case "unfavourable"
With .AddComment
.Text Now & MyString2
End With
Case Else
'Do nothing
End Select
End With
End Sub