Need help with UserName entry

[McBarker]

I have a spreadsheet which I share with four other people, and I need to
have their login username (the variable is called sName) as part of their
data entry. What I need is a way to fix the following problem.

Their last line of data entry is a Time variable which is entered into
Column N, and is used later to verify that data has been entered in that
particular Row. One of the problems is that after they enter a time, not all
of them use the same key to move to another cell. For example, when
inputting the time in Cell "N7", User1 uses the [Enter] key, causing Cell
"N8" to be the new active cell; User2 uses the arrow keys, which means that
there are four possible destinations for the new active cell; and all
occasionally hit [PageUp] or [PageDown] in error.

Given the above problem, I need WorkSheet code to automatically enter sName
in the next Column of the row directly after the Time entry, i.e.

12:06 is entered in "N7", so User login should automatically appear in "O7"
13:30 is entered in "N8", so User login should automatically appear in "O8"
etc.

Any help would be appreciated.

Regards
McBarker

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "N:N"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.offest(0, 1).Value = sName
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[McBarker]

I have a spreadsheet which I share with four other people, and I
need to have their login username (the variable is called sName) as
part of their data entry. What I need is a way to fix the following
problem.

Their last line of data entry is a Time variable which is entered
into Column N, and is used later to verify that data has been
entered in that particular Row. One of the problems is that after
they enter a time, not all of them use the same key to move to
another cell. For example, when inputting the time in Cell "N7",
User1 uses the [Enter] key, causing Cell "N8" to be the new active
cell; User2 uses the arrow keys, which means that there are four
possible destinations for the new active cell; and all occasionally
hit [PageUp] or [PageDown] in error.

Given the above problem, I need WorkSheet code to automatically
enter sName in the next Column of the row directly after the Time
entry, i.e.

12:06 is entered in "N7", so User login should automatically appear
in "O7" 13:30 is entered in "N8", so User login should automatically
appear in "O8" etc.

Any help would be appreciated.

Regards
McBarker

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "N:N"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.offest(0, 1).Value = sName
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Bob, your code didn't work. It seemed to not do anything. I managed to come
up with the following working solution (in case anyone else has a similar
problem). If anyone sees a potential problem with the following code, please
let me know. Thanks

Regards
McBarker

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
Dim sName As String
Dim TimeEntered As Boolean

Application.EnableEvents = False
For Each cell In Range("N7:N31")
.NumberFormat = "hh:mm"
TimeEntered = False
.Value = TimeValue(TimeStr)
TimeEntered = True
Do While TimeEntered = True
.Offset(0, 1) = sName
TimeEntered = False
Loop
Next
Application.EnableEvents = True
Exit Sub