Double Click Macro VBA - entering 'signature' user id in cells

[ashtally]

Hi,

I have used this coding before but for a maximum of two column ranges. In this case I have 5 columns which we want to be able to be 'signed' by double-clicking and auto entering the user ID time stamp. Can anyone help fix this coding to allow for this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim myrange As Range
    Dim myCalc As XlCalculationState
    Dim ws As Worksheet
    Dim r As Long
    
    Application.ScreenUpdating = False
 
    Set myrange = ActiveSheet.Range("Signature")

    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    
    If Intersect(Target, myrange) Is Nothing Then
        Exit Sub
    End If
 
        If ActiveCell > 0 Then
        MsgBox "Already Actioned", vbInformation, "Error"
        Cancel = True
        Exit Sub
    End If
 
    If MsgBox("Confirm Submission / Authorisation?", vbYesNo + vbQuestion, "Submit / Authorise") = vbNo Then
        Cancel = True
        Exit Sub
    End If

    myCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    r = Target.Row
 
    If ActiveSheet.Cells(Target.Row, 17) <= 0 Then
           ActiveSheet.Cells(r, 17).Value = Environ("Username") & " " & Format(Now, "hh:mm dd-mmm-yy")
    ElseIf ActiveSheet.Cells(Target.Row, 17) > 0 Then
               ActiveSheet.Cells(r, 18).Value = Environ("Username") & " " & Format(Now, "hh:mm dd-mmm-yy")
     
    End If
 
    Cancel = True
 
    Application.Calculation = myCalc
    Application.ScreenUpdating = True
End Sub

Thank you!

[Olly]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Olly]

Still not quite clear what you actually want to achieve. A screenshot is pretty useless (I'm not going to try to recreate your workbook structure from an image) - an example workbook showing what you are trying to achieve would be more useful.

If it's as simple as: when a user double-clicks a cell in columns Q:U, the username and current time are entered in that cell, then you can simply use:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("Q:U")) Is Nothing Then
        If Target.Value > 0 Then
            MsgBox "Already Actioned", vbInformation + vbOKOnly, "Error"
        ElseIf MsgBox("Confirm Submission / Authorisation?", vbYesNo + vbQuestion, "Submit / Authorise") = vbYes Then
            Target.Value = Environ("UserName") & Format(Now, "hh:mm dd-mm-yy")
        End If
        Cancel = True
    End If
End Sub