Current time "sticking" in a userform

[Bandy]

I have a user form as part of a timeclock system and the time is shown in a text box so that when clock in or out button is activated the time is entered into the spreadsheet(column A for clock in and column B for clock out).

That works beautifully, but with one exception. After some time has passed the first clock entry shows the time a few minutes after the last, or previous, clock action rather than the current system time. The next and subsequent entries to the spreadsheet show the correct time.

It would appear therefore that the time on the user form is not being updated after a pause. Can anyone help me track down a solution to this problem

 

[Zack Barresse]

Hello Bandy, welcome to the board!

Could you post all the code from your UserForm?

 

[Bandy]

Hello and thank you for the welcome.

As you have probably guessed I am new to Excel but am enjoying the learing experience it is giving me

I think I have solved the problem by adding the following to the user form i.e "frmTime.TxtClock.Value = Time"

certainly it works for a few hours delay and I am trying overnight today and will let you know in the morning if it has been successful.

In the meantime thank you for your response and be assured that if I'm wrong I'll be straight back.

In the mean time this is the revised code you requested, and any comments you may have will be very welcome since I'm sure this isn't the most elegant or best way of doing it.


Private Sub CmdClear_Click()
'Call UserForm_Initialize
frmTime.TxtClock.Value = Time
End Sub

Private Sub cmdClockIN_Click()
frmTime.TxtClock.Value = Time
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, 2) = TxtClock.Value
ActiveCell.Offset(0, 1) = ListBox1.Value
TxtDate.Value = Date
TxtClock.Value = Time()

End Sub

Private Sub CmdClockOUT_Click()
frmTime.TxtClock.Value = Time
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

Private Sub CmdClose_Click()
Unload Me
End Sub

Private Sub ListBox1_Click()

End Sub

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

With ListBox1
.AddItem "Trevor Austin"
.AddItem "Terry Brown"
.AddItem "Steve Barwood"
.AddItem "Andy Claxton"
.AddItem "Daniel Evans"
.AddItem "Ivan Farrow"
.AddItem "Alan Framingham"
.AddItem "Peter Framingham"
.AddItem "Shane Framingham"
.AddItem "Terry Harwood"
.AddItem "Gary Henson"
.AddItem "Paul Hornsby"
.AddItem "Jonathan Kennedy"
.AddItem "Roger Kightly"
.AddItem "Danny Loveridge"
.AddItem "Shane Mortimer"
.AddItem "Bryan Whitcombe"
End With

End Sub

 

[Zack Barresse]

I'm not really sure. We could shorten it somewhat ...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> LastCell <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdClear_Click()
    frmTime.TxtClock.Value = Time
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
  
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdClockIN_Click()
    frmTime.TxtClock.Value = Time
    <SPAN style="color:#00007F">Set</SPAN> LastCell = Cells(Rows.Count, "A").End(xlUp).Offset(1)
    LastCell.Value = TxtDate.Value
    LastCell.Offset(0, 2).Value = TxtClock.Value
    LastCell.Offset(0, 1).Value = ListBox1.Value
    TxtDate.Value = Date
    TxtClock.Value = Time
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
  
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdClockOUT_Click()
    frmTime.TxtClock.Value = Time
    <SPAN style="color:#00007F">Set</SPAN> LastCell = Cells(Rows.Count, "A").End(xlUp).Offset(1)
    LastCell.Value = TxtDate.Value
    LastCell.Offset(0, 3).Value = TxtClock.Value
    LastCell.Offset(0, 1).Value = ListBox1.Value
    TxtDate.Value = Date
    TxtClock.Value = Time
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
  
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdClose_Click()
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
  
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    <SPAN style="color:#00007F">Dim</SPAN> Arr() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> LastCell = Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Arr = Array("Trevor Austin", "Terry Brown", "Steve Barwood", "Andy Claxton", "Daniel Evans", "Ivan Farrow", _
        "Alan Framingham", "Peter Framingham", "Shane Framingham", "Terry Harwood", "Gary Henson", "Paul Hornsby", _
        "Johnathan Kennedy", "Roger Kightly", "Danny Loveridge", "Shane Mortimer", "Bryan Whitcombe")
    frmTime.TxtClock.Value = Time
    LastCell.Offset(0, 1).Value = ListBox1.Value
    TxtDate.Value = Date
    TxtClock.Value = Time
    ListBox1.List = Arr
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


.. but not by much.

 

[Bandy]

Once again thank you for your comments, which I'll take on board. I like your efforts better than mine, particularly since there are areas there that I have not used before.

By the way the line "frmTime etc" appears to have solved the problem I had with wrong time entries.

Taking it one day at a time I'm gradually making a few more strides in understanding. Thank you

 

[Zack Barresse]

You're very welcome Brandy.

Have a great holidays!