Insert date/user when double click cells within column

[Tom98]

Hi,

I would like my work sheet to populate the double clicked cell with the username when double clicking any cells within column BX and populate the double clicked cell with the date when double clicking any cells within column BW.

Or even better, to update both username and date in the specific row when either BX or BW are double clicked.

Many thanks

 

[Peter_SSs]

update both username and date in the specific row when either BX or BW are double clicked.

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("BW:BX")) Is Nothing Then
    Cancel = True
    Intersect(Target.EntireRow, Columns("BW:BX")).Value = Array(Environ("username"), Date)
  End If
End Sub

 

[Tom98]

Peter,

That works perfectly.

Thank you very much!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

 

[sambuka]

@Peter_SSs
Thank you for solving this. I used the above code for some of my project and adapt. Can this be expanded to include 3 seperate "double clicks"? Column A - one set of users will double click and sign off, Column B - second set of users will double click and sign off, Column C - Users will double click to fill out that row.

 

[sambuka]

@Peter_SSs - Figured it out

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    Cancel = True
    Intersect(Target.EntireRow, Columns("A:A")).Value = Array(Environ("username") & " (" & Date & ") (" & Time & ")")
  End If
    If Not Intersect(Target, Columns("B:B")) Is Nothing Then
    Cancel = True
    Intersect(Target.EntireRow, Columns("B:B")).Value = Array(Environ("username") & " (" & Date & ") (" & Time & ")")
  End If
      If Not Intersect(Target, Columns("C:C")) Is Nothing Then
    Cancel = True
    Intersect(Target.EntireRow, Columns("C:C")).Value = Array(Environ("username") & " (" & Date & ") (" & Time & ")")
  End If
End Sub

 

[Peter_SSs]

@Peter_SSs - Figured it out

Good news!

However, you could also consider this much shorter version (check the date format that you want).

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("A:C")) Is Nothing Then
    Cancel = True
    Target.Value = Environ("username") & Format(Now, " (dd/mm/yyyy) (hh:mm:ss AM/PM)")
  End If
End Sub

 

[Peter_SSs]

Thanks for updating your profile details.