Time sticking in an Excel userform

  Bandy 19:22 20 Dec 05
Locked

I have a userform in an Excel spreadsheet to enter time into a payroll system.

The time is entered into the spreasheet via a userform which contains a textbox for the time and clock in and clock out buttons.

This works well and the clock in time is entered into column b with the clock out time entered into column c.

There is however one problem, in that if there is a long delay between entries then the time entered is incorrect. The time that is entered is a few minutes later than the previous entry rather than the current time. This only happens with the first entry after the delay, the second and subsequent entries show the correct time.

Can anyone help me along the path to a solution please?

  VoG II 19:29 20 Dec 05

Are you saying that the user enters the time into a textbox but the resultant entry in the spreadsheet is wrong? Have you checked that the input times are valid? Here's an example using an InputBox but the principle is the same.

Sub GetATime()

Dim TheString As String

Dim TheTime As Double

TheString = Application.InputBox("Enter A Time")

TheString = Left(TheString, 5)

If IsDate(TheString) Then

TheTime = TimeValue(TheString)

MsgBox (TheTime)

Else

MsgBox "Invalid time"

End If

End Sub

  Bandy 19:40 20 Dec 05

No VoG sorry but I didn't explain it very well.

The time is currently shown in the text box which show the time of the last action.

This is the code in the Clock Out section command button

Private Sub CmdClockOUT_Click()
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 0) = TxtDate.Value
ActiveCell.Offset(0, 3) = TxtClock.Value
ActiveCell.Offset(0, 1) = ListBox1.Value
TxtDate.Value = Date
TxtClock.Value = Time

End Sub

Does that help?

  VoG II 20:23 20 Dec 05

Um, what is the code that places the time in the TextBox?

I'm unclear as to why you need to use a userform for this.

  Bandy 21:23 20 Dec 05

I'm using a form because it is easier for some of the people involved to clock in and out. Some are able to use a mouse but not a keyboard.

The time in the txt box is simply
TxtClock.Value = Time

  Bandy 21:27 20 Dec 05

If it is any help the text box time is really only to show the time they clocked in or out and the "TxtClock.Value = Time" code is in the initialise section of the form

  VoG II 21:34 20 Dec 05

So can't you include

UserForm1.TxtClock.Value = Time

as the first line after Private Sub CmdClockOUT_Click()

or am I being even denser than usual?

  Bandy 22:39 20 Dec 05

No VoG you certainly were no being dense I think it was more likely that I was having what is currently labelled as "a senior moment"

Tried your suggestion and it appears to be working well. I'll try it now with a longer delay overnight and in the meantime tick the resolved box.

Another success for you and thank you for your help

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…