Append text to a cell when a label is clicked

[rdoulaghsingh]

Good evening experts!

I have a spreadsheet with a few hundred rows that I'm trying to capture reviews on. Column A cells has "Reviewed" (using a label currently) all the way down. I'm open to suggestions if there is a better/easier method instead of using labels for click events.

I'm looking for a VBA code that will insert the username and the last reviewed date and time to the cell immediately to the right (column B) of the "Reviewed" cell when "Reviewed" is clicked. Here's the catch: I want it to append new users if the user does NOT exists in the cell, and if the user already exists I want it to keep the current user, but overwrite the last reviewed time. I'm currently using

.Value = "User: " & Environ("username") & " Last reviewed: " & Now()

for obtaining the username and last reviewed time. Is there any way to do this?

Thanks in advance!

 

[Micron]

Is there a way to know what row a label control is in? I think you'd need that for sure and I have not found any property that would reveal that. Methinks I'd use Worksheet_BeforeDoubleClick to trigger the code because you definitely can know the row thus which adjacent cell(s) need modifying. Would be easier if you split the name and time into 2 columns.

 

[rdoulaghsingh]

Is there a way to know what row a label control is in? I think you'd need that for sure and I have not found any property that would reveal that. Methinks I'd use Worksheet_BeforeDoubleClick to trigger the code because you definitely can know the row thus which adjacent cell(s) need modifying.

@Micron That I'm not sure of. I'm ok with just using plain text as well and just formatting the text. If the cell is clicked then add the text with the conditions. Not sure what the code would look like to add and append in the adjacent rows though.

 

[Micron]

IMO, you don't want anyone changing info just because they click or tab into a cell. That's why I suggested the double click event, which might look like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

With Target
   If .Offset(0, 1) = "" Then
      .Offset(0, 1) = "User: " & Environ("username")
   End If
 
   .Offset(0, 2) = " Last reviewed: " & Now()
   Cancel = True
End With

End Sub

However, that will place the info in 2 separate columns since the construct is simpler. If you're not ok with that, would have to be edited. It's also based on my interpretation of

I want it to append new users if the user does NOT exists in the cell

which now that I think of it, it probably not correct. Maybe that means if the user name is in the cell, only edit the date, otherwise insert a line break and add this user

 

[rdoulaghsingh]

IMO, you don't want anyone changing info just because they click or tab into a cell. That's why I suggested the double click event, which might look like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

With Target
   If .Offset(0, 1) = "" Then
      .Offset(0, 1) = "User: " & Environ("username")
   End If
 
   .Offset(0, 2) = " Last reviewed: " & Now()
   Cancel = True
End With

End Sub

However, that will place the info in 2 separate columns since the construct is simpler. If you're not ok with that, would have to be edited. It's also based on my interpretation of

which now that I think of it, it probably not correct. Maybe that means if the user name is in the cell, only edit the date, otherwise insert a line break and add this user

@Micron Thanks for the code sample. I'll give it a try when I get back to my desk. I'm trying to append in the same cell. So if user A clicks the A1 with "Reviewed", it stamps their username, date and time in B1. If user B comes along and clicks A1 then it would add an entry below User A's in B1. If user A goes back and click "Reviewed" again in A1, then it would change the date and time in B1 next to their username (or overwrite with a new entry) because there is already an entry in the cell for user A. Hopefully that makes sense.

 

[Micron]

I think you'll find that line wrapping will be confusing unless you resize the row, otherwise line wraps will make appended names to not show in the sheet at some point and not at all in the formula bar unless you navigate over them with arrow key. Could concatenate names, as in Bob, Joe, Carol and then the date/time. That may not show you all the names in a cell but should make it obvious that there are more. To append, would look more like

With Target
   If InStr(1, .Offset(0, 1).Value, Environ("username")) = 0 Then
      .Offset(0, 1).Value = .Offset(0, 1).Value & Chr(10) & Environ("username")
   End If
   
   .Offset(0, 2) = " Last reviewed: " & Now()
   Cancel = True
End With

but again, name and date are split into columns in that code.

 

[rdoulaghsingh]

I think you'll find that line wrapping will be confusing unless you resize the row, otherwise line wraps will make appended names to not show in the sheet at some point and not at all in the formula bar unless you navigate over them with arrow key. Could concatenate names, as in Bob, Joe, Carol and then the date/time. That may not show you all the names in a cell but should make it obvious that there are more. To append, would look more like

With Target
   If InStr(1, .Offset(0, 1).Value, Environ("username")) = 0 Then
      .Offset(0, 1).Value = .Offset(0, 1).Value & Chr(10) & Environ("username")
   End If
  
   .Offset(0, 2) = " Last reviewed: " & Now()
   Cancel = True
End With

but again, name and date are split into columns in that code.

@Micron Thanks for the swift reply. Unfortunately, this doesn't yield the desired results for appending a NEW user in B column if there are existing users who have reviewed.

 

[rdoulaghsingh]

IMO, you don't want anyone changing info just because they click or tab into a cell. That's why I suggested the double click event, which might look like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

With Target
   If .Offset(0, 1) = "" Then
      .Offset(0, 1) = "User: " & Environ("username")
   End If
 
   .Offset(0, 2) = " Last reviewed: " & Now()
   Cancel = True
End With

End Sub

However, that will place the info in 2 separate columns since the construct is simpler. If you're not ok with that, would have to be edited. It's also based on my interpretation of

which now that I think of it, it probably not correct. Maybe that means if the user name is in the cell, only edit the date, otherwise insert a line break and add this user

@Micron I think this points me in the right direction. Thank you!