Excel 2003 - Double Click to enter user ID/ Windows ID?

[sethmeister21]

In Excel 2003 - how can I set VBA to insert user ID/ Windows ID into a cell when you double click mouse?

All help gratefully received.


regards
S

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Value = Environ("username")
End Sub

 

[sethmeister21]

Hi VoG, great this works fine....but how do i restrict to column C so that users do not make errors all over the sheet?

 

[VoG]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 3 Then
    Cancel = True
    Target.Value = Environ("username")
End If
End Sub

 

[sethmeister21]

Great that worked nicely, thanks so much!

 

[sethmeister21]

OK, sorry about this - one more question.....How about entering a timestamp by double-clicking on column D? This would display date and time e.g. 20-JAN-2010 11:15

 

[VoG]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Column
    Case 3: Cancel = True: Target.Value = Environ("username")
    Case 4: Cancel = True: Target.NumberFormat = "dd-mmm-yyyy hh:mm": Target.Value = Now
End Select
End Sub

 

[sethmeister21]

Perfect. thanks VoG.