Inserting Comments with VBA

  • Thread starter Thread starter akemeny
  • Start date Start date
A

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??
 
Try this idea.

Sub AddMOREtocomment()
With Range("P4")
.Comment.Text Text:=.Comment.Text & Chr(10) & "more"
End With
End Sub
 
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??
 
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.
 
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??
Click to expand...

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
 
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??
Click to expand...

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
 
Back
Top