VBA Double Left Click Error

[Mr trying to learn]

So I used to work with a co-worker who used this excel macro to do his daily checklist and he sent it to me a long time ago. I never had the need to use it till now, when you double left click it timestamps the cell with the initials of your logon username and the date which is perfect for my daily checklist. The problem is that I am having a error when I try to double click.

I am using windows 10 and the latest excel.

So say the time is 12:57 pm and my name is Barry Kern I would double click the cell and it would do this. BK 12:57 and be a bright green color.



The exact error when I double click.

I then open the debugger

Private Sub Worksheet_BeforeDoubleClick(<wbr>ByVal Target As Range, Cancel As Boolean)
   UserName = Application.UserName
    Cancel = True
   
    
    Dim d() As String
    r = UserName
    d() = Split(r, ", ")
    i = Left(d(1), 1) & Left(d(0), 1)
   
    
    
    Target.Interior.Color = vbGreen
    Target.Value = i & " " & FormatDateTime(Time, 4)
End Sub

I have tried messing with his code. I deleted the (i) line of code because I saw that it was highlighted and its does timestamp the cell and make it green but does not put in the initials which I really need because this file is in a shared folder with many people working on the checklist and I like to keep track of who checks what off.

 

[mikerickson]

The i line is where the initials come from.

To protect against that, I'd change the code to

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   UserName = Application.UserName
    Cancel = True
   
    
    Dim d() As String
    r = UserName
    [COLOR="#FF0000"]d() = Split(r & "," , ",")[/COLOR]
    i = Left(d(1), 1) & Left(d(0), 1)
   
    
    
    Target.Interior.Color = vbGreen
    Target.Value = i & " " & FormatDateTime(Time, 4)
End Sub

It looks like the problem is caused by a username without a comma. The code supplies the needed comma.

 

[JoeMo]

My guess is the UserName doesn't have the format the code is designed to handle. Try replacing the code with the altered version below. Run it and see what the format of the UserName looks like in the pop-up message box. Then post exactly what you see in the message box.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   UserName = Application.UserName
    Cancel = True
   
    
    Dim d() As String
    r = UserName
    MsgBox r
'    d() = Split(r, ", ")
'    i = Left(d(1), 1) & Left(d(0), 1)
   
    
    
'    Target.Interior.Color = vbGreen
'    Target.Value = i & " " & FormatDateTime(Time, 4)
End Sub

 

[Norie]

That code appears to be trying to split the username, using ', ' as a delimiter, so as to extract the initials.

If the username doesn't have a comma in it then the code won't' be able to split it properly.

Try this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim UName As String
Dim arr As Variant
Dim I As Long

    Cancel = True
    
    UName = Application.UserName
    
    arr = Split(Replace(UName, ",", ""), " ")
    
    UName = ""
    
    For I = LBound(arr) To UBound(arr)
        UName = UName & Left(Trim(arr(I)), 1)
    Next I
    
    Target.Interior.Color = vbGreen
    
    Target.Value = UName & " " & FormatDateTime(Time, 4)
    
End Sub