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

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in A1 or B1 and exit the cell. The comment will automatically be added to B2. The comment will change each time you change the value in A1 or B1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
    With Range("B2")
        .ClearComments
        .AddComment Range("A1") & " " & Range("B1") & " " & Application.UserName & " " & Date
    End With
End Sub

 

[EwEn999]

This is a great start, however I don't think I fully explained what I hoped to achieve. I'd like to implement this into a work schedule, which has multiple sites, multiple shifts and multiple officers.​

I'll try and explain what the table looks like;
A1 = the site name
B1:H1 = the days of the week
A2:A6 = employees name
B2:H6 = the shifts that are scheduled i.e. days & nights
J1 = the site pay rate

What I require the comment to say is (only when an shift is entered in to B2:H6)
Line 1: The authors name
Line 2:The employees name (A2)
Line 3: The site name (A1)
Line 4: The shift times i.e. if a day shift is scheduled, 0600-1800 appears in the comment
Line 5: The site pay rate (J1)
Line 6: The time and date in which the entry was made into B2

I hope this makes sense and I apologise if this comes across as pedantic.
If its not obvious I've never attempted anything like this before.
Thanks for your previous reply, I found it very impressive.

 

[mumps]

I'm not clear on which cells you want to contain the comment when a shift is entered in B2:B6.

 

[EwEn999]

I'm not clear on which cells you want to contain the comment when a shift is entered in B2:B6.

If B2 has a specific shift typed into it, the comment would also appear in B2. The information displayed in the comment is conditional on what is typed in the target cell. I hope this clear, I apologise for my lack of technical language.

 

[My Aswer Is This]

You said:

If B2 has a specific shift typed

What is a shift?


​

 

[EwEn999]

You said:

If B2 has a specific shift typed

What is a shift?


​

Day shift or night shift

 

[mumps]

So do you want the comment to be added to each cell in B2:B6 as a shift is entered in each cell?

 

[EwEn999]

So do you want the comment to be added to each cell in B2:B6 as a shift is entered in each cell?

Yes please, thanks for being patient with me and my lack of clearly explaining what I hoping to achieve

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:B6")) Is Nothing Then Exit Sub
    With Range("B" & Target.Row)
        .ClearComments
        .AddComment Application.UserName & Chr(10) & Range("A" & Target.Row) & Chr(10) & Range("A1") & Chr(10) & Target & Chr(10) & Range("J1") & Chr(10) & Now()
        .Comment.Shape.TextFrame.AutoSize = True
    End With
End Sub