Excel Userform Comment Log

[TessieBear99]

I'd like to create a Userform which can be a log/tracker for comments.

I'm working on a spreadsheet with many rows of data and at the end of each row the plan is to have a button to view/add comments for that row. I'd like it to open up a Userform similar to the picture below (please note this is not my screenshot, it has been copied from this question: VBA code for a comment tracker on a userform):

As shown above, I'd like it to have (if possible) the name of the user with a date & time stamp, followed by the previously entered comments.
What isn't shown above which I'd like to have is an "Add comment" button which would bring up a box for the user to write their comment in then submit, and come back to this original Userform which would refresh to include the new comment.

The other thing is that it's a growing and changing worksheet and there are 109 rows of data in my table at the moment, and I will need to have this functionality applied to each row and all new rows going forward.

Is this even possible, and if it is, does someone have any idea how I could do this?

Thanks!

 

[DanteAmor]

Hi @TessieBear99 . Thanks for posting on MrExcel board.

These are my suggestions:

at the end of each row the plan is to have a button to view/add comments for that row

1. If your idea is to have a button on each line, I do not recommend it since it is a very laborious task.​

I suggest you simply select the row where you want to update the comment and open the userform.​

2. The next recommendation is the controls in your userform:​

a) A TextBox1 on the form to capture the new comment​

b) TextBox2 to display all comments.​

c) Of course, a button to add the new comment.​

d) A button to save the comment.​

e) A button to cancel.​

​

As shown in the screenshot below:​

3) In both textbox change the MultiLine property to True​

4) We need to store the history of the comments on that line in a column.​

In my tests I put the column "M". Change to the letter of the column where you are going to save the comments in this line of the macro.​

Const col As String = "M"

5) Copy the entire macro into your userform:​

Option Explicit

Const col As String = "M"

Private Sub CommandButton1_Click()
  Dim cmt As String
  If TextBox1.Value = "" Then
    MsgBox "Enter comment"
    TextBox1.SetFocus
    Exit Sub
  End If
  cmt = Environ$("username") & " " & Format(Now, "dddd, dd mmm yyyy hh:mm am/pm") & Chr(10) & TextBox1.Value
  With TextBox2
    If .Value = "" Then .Value = cmt Else .Value = .Value & Chr(10) & Chr(10) & cmt
  End With
End Sub

Private Sub CommandButton2_Click()
  Range(col & ActiveCell.Row).Value = TextBox2.Value
  Unload Me
End Sub

Private Sub CommandButton3_Click()
  Unload Me
End Sub

Private Sub UserForm_Activate()
  TextBox2.Value = Range(col & ActiveCell.Row).Value
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​