Adding comments automatically

[EwEn999]

Hi, before i start to explain my problem, I'm a complete rookie when it comes to VBA, however I've got a fair amount of knowledge when it comes to formulas.

I would like to know if its possible to add comments automatically, depending on the contents of the cell.
For example the comment is located in "B2", and id like the contents of "a1" and "b1" to show in the comment box, plus the author's name (name of who ever is logged on) and the current time and date.

I'm not totally sure if this is possible, but any help would be apprenticed

 

[strooman]

I don't know what your actual setup is but presume this simplified layout where Range("J2:K6") is your lookup table for the shifts. Shifts are:
ES, MS, LS, NS, FREE and stands for EarlyShift, MidShift, LateShift, NightShift and Free.


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD="bgcolor: #0070C0"]The Site Name[/TD]
[TD="bgcolor: #FFFF00"]Monday[/TD]
[TD="bgcolor: #FFFF00"]Tuesday[/TD]
[TD="bgcolor: #FFFF00"]Wednesday[/TD]
[TD="bgcolor: #FFFF00"]Thursday[/TD]
[TD="bgcolor: #FFFF00"]Friday[/TD]
[TD="bgcolor: #FFFF00"]Saturday[/TD]
[TD="bgcolor: #FFFF00"]Sunday[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]Shift[/TD]
[TD="bgcolor: #FFFF00"]Time[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD="bgcolor: #FFFF00"]Maria Anders[/TD]
[TD]ES[/TD]
[TD]MS[/TD]
[TD]LS[/TD]
[TD]NS[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD][/TD]
[TD]ES[/TD]
[TD]07:00-15:00[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD="bgcolor: #FFFF00"]Ana Trujillo[/TD]
[TD]FREE[/TD]
[TD]ES[/TD]
[TD]MS[/TD]
[TD]LS[/TD]
[TD]NS[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD][/TD]
[TD]MS[/TD]
[TD]12:00-20:00[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD="bgcolor: #FFFF00"]Antonio Moreno[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]ES[/TD]
[TD]MS[/TD]
[TD]LS[/TD]
[TD]NS[/TD]
[TD]FREE[/TD]
[TD][/TD]
[TD]LS[/TD]
[TD]15:00-23:00[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD="bgcolor: #FFFF00"]Thomas Hardy[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]ES[/TD]
[TD]MS[/TD]
[TD]LS[/TD]
[TD]NS[/TD]
[TD][/TD]
[TD]NS[/TD]
[TD]23:00-07:00[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD="bgcolor: #FFFF00"]Christina Berglund[/TD]
[TD]NS[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[TD]ES[/TD]
[TD]MS[/TD]
[TD]LS[/TD]
[TD][/TD]
[TD]FREE[/TD]
[TD]FREE[/TD]
[/TR]
</tbody>[/TABLE]


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Right (or double) click on Sheet1 in the left pane
5. You will be presented with a blank module in the
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. Type a shift abbreviation somewhere in the range("B2:H6")

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strAuthor, strEmployee, strSiteName, strShift, strSitePayRate As String
Dim dtmDate As Date
Dim intRow As Integer

    If Intersect(Target, Range("B2:H6")) Is Nothing Then Exit Sub
    intRow = Target.Row
    strAuthor = Application.UserName
    strEmployee = Application.WorksheetFunction.Index(Range("$A$1:$A$6"), Target.Row)
    strSiteName = Range("A1").Value
    strShift = Application.VLookup(Target.Value, Range("J2:K6"), 2, False)
    strSitePayRate = ""
    dtmDate = CDate(Date)
    With Target
        .ClearComments
        .AddComment strAuthor & vbNewLine _
        & strEmployee & vbNewLine _
        & strSiteName & vbNewLine _
        & strShift & vbNewLine _
        & strSitePayRate & vbNewLine _
        & dtmDate
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
End Sub

 

[EwEn999]

This brilliant, exactly what I was looking for. I’ve just a few formatting questions.

The application.username line I’d like to appear bold.
strSitePayRate = “” isn’t formatting either. When the target cell is formatted to currency, it isn’t being carried over to the comment.

Thanks again @strooman

 

[strooman]

Adjusted a little bit. First, I don't know what SitePayRate means so I can't help you any further with that.
To format the application.username run this code and the application.username will be Bold (and in red). When you don't want it in red just replace 3 with 1
.Characters(1, lBreak).Font.ColorIndex = 3
.Characters(1, lBreak).Font.ColorIndex = 1

I also removed vbNewLine. Replaced that with chr(10). So the new code is now:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strAuthor, strEmployee, strSiteName, strShift, strSitePayRate As String
Dim dtmDate As Date
Dim intRow As Integer
Dim cmt As Comment
Dim lBreak As Long

    If Intersect(Target, Range("B2:H6")) Is Nothing Then Exit Sub
    intRow = Target.Row
    strAuthor = Application.UserName
    strEmployee = Application.WorksheetFunction.Index(Range("$A$1:$A$6"), Target.Row)
    strSiteName = Range("A1").Value
    strShift = Application.VLookup(Target.Value, Range("J2:K6"), 2, False)
    strSitePayRate = ""
    dtmDate = Now()
    
    With Target
        .ClearComments
        .AddComment strAuthor & Chr(10) _
        & strEmployee & Chr(10) _
        & strSiteName & Chr(10) _
        & strShift & Chr(10) _
        & strSitePayRate & Chr(10) _
        & dtmDate
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
    
    Set cmt = Target.Comment
    If cmt Is Nothing Then
        Exit Sub
    End If
    
    'find the line break which is Chr(10)
    lBreak = InStr(1, cmt.Text, Chr(10))
  
    'format username in red and bold
    With cmt.Shape.TextFrame
        .Characters.Font.Bold = False
        .Characters(1, lBreak).Font.ColorIndex = 3
        .Characters(1, lBreak).Font.Bold = True
        .Characters(lBreak + 1, Len(cmt.Text)).Font.ColorIndex = 1
    End With
End Sub

Thanks for the feedback.

 

[EwEn999]

What I mean by site rate is the wage that the employee gets paid for working on this particular site. So if that information was in cell J8.
J8 = “£9.00” and is formatted to currency.
When I put this information into the above code, and displayed the comment, it converted it back to a plain number. I would like the comment to read it as “£9.00 p/h”. Thanks

 

[strooman]

With 9,00 in J8 something like this:

strSitePayRate = ChrW(163) & Range("J8").Value & " p/h"

Pay attention to the comma in 9,00 perhaps you should use . (point). This has to do with your regional settings.

 

[EwEn999]

So far everything is working perfectly, but now I'm having trouble trying to expand it on multiple tables on the same worksheet. The design of the table is nearly identical to the example @strooman in reply #11 apart from the name of the site is different. Is it possible to run the macro as one continuous script, or am going have to break it into separate modules?

 

[strooman]

now I'm having trouble trying to expand it on multiple tables on the same worksheet.
[ . . . ]
Is it possible to run the macro as one continuous script, or am going have to break it into separate modules?

A lot is possible with Excel but it depends on the layout you have. You can present us some (dummy)data with your layout.
Another question is, would you like the macro to fire everytime you type a shift in the cell or do you want to fire the macro only one time when you finished inserting all your shifts?

 

[EwEn999]

A lot is possible with Excel but it depends on the layout you have. You can present us some (dummy)data with your layout.
Another question is, would you like the macro to fire everytime you type a shift in the cell or do you want to fire the macro only one time when you finished inserting all your shifts?

I'm unable to create another table currently due to restrictions on my current PC, but it is going to be identical to the example you used in post #11 , just starting at "A8".

And yes adding comments every time a shift is entered would be best.

The final worksheet is going to have 14 tables of various sizes, i don't know if this will create more work. I'm just trialing everything on a test worksheet at the moment.