Double Click to enter User ID

[monk22]

Guys, i have some code that allows my users to enter their user ID on double clicking. The worksheet code below shows how on double clicking into Colum 3 (If Target.Column = 3 Then) then the user ID will appear. Q: How do i get the code to work in Columns 3, 5 and 7 but not those inbetween?! I tried using "or" but it allowed the ID to be entered into all columns! i.e. 3,4,5,6 and 7 which is not what I require. In short I need the ID to appear on double clik into every other column. Thanks. See below for code:



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

Dim user As String
'This refers to the function ReturnUserName, which takes the name from the NT environment

If Target.Column = 3 Then
Target = ReturnUserName
Cancel = True
End If

End Sub



Many Thnaks
Monk22

 

[VoG]

Try

 If Target.Column = 3 Or Target.Column = 5 Or Target.Column = 7 Then

Edit:

This is better

If WorksheetFunction.IsOdd(Target.Column) Then

 

[RichardU]

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

Dim user As String 
'This refers to the function ReturnUserName, which takes the name from the NT environment 

If Target.Column = 3 Then 
Target = Environ("username")
Cancel = True 
ActiveCell.Offset(0,2).Value = ActiveCell.Value
ActiveCell.Offset(0,4).Value = ActiveCell.Value
End If

End Sub

Try that

 

[Peter_SSs]

Edit:

This is better

If WorksheetFunction.IsOdd(Target.Column) Then

But then the code would run for columns A, I, K, M, ... , IU as well, which (by my reading at least) is not what the OP wants.

 

[VoG]

Hi Peter

In short I need the ID to appear on double clik into every other column.

Maybe I took that too literally.

 

[Peter_SSs]

Hi Peter

In short I need the ID to appear on double clik into every other column.

Maybe I took that too literally.

..or maybe you are right.

 

[monk22]

Hi guys, thnak you very much for your help. I used the solution from VoG II because I only had a limited number of column to apply the code to.

New Q: How do i combine this with resticting to every other row? The code currently successfully applies to every other column (or the columns I specify) but the entire column. With regards to setting every other row Ive tried AND Target.Row but this does not seem to work.

Thanks in advance

 

[VoG]

Try

If WorksheetFunction.IsOdd(Target.Column) And WorksheetFunction.IsOdd(Target.Row) Then

which will restrict it to odd columns and rows.

 

[monk22]

This gives me "run-time error '438':"

 

[VoG]

This works for me

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