Updating a label of a form (by use of timer)

[Wil Moosa]

Every second the time is changed in a specific cell. A label of a specific form is linked to this cell with the code:

Label3 = Sheets("Dagteller").Range("A50").Text

The result is that the label only shows the time as it was when the form was opened (it does no updating, counting or whatever). How do I get the changing time changing in the label too?

 

[Todd Bardoni]

Select your form and in the Properties select ShowModal=False.

 

[Wil Moosa]

Changing this does not give the wanted response...

 

[Todd Bardoni]

Can you post the code you have?

 

[Todd Bardoni]

Here's the code I used. It works fine:

Sub time()
Application.OnTime Now + TimeValue("00:00:01"), "my_Procedure"
End Sub

Sub my_procedure()
UserForm1.Show
Range("A1") = Now()
UserForm1.Label1 = Format(Range("a1"), "hh:mm:ss")
time
End Sub

 

[Wil Moosa]

I played a bit with the above code but did not get it functional the way it should. What did happen is that the form turns up, shows a static time and when the form is closed it comes back immediately with a new time.

In response to your question I add my code:

Dim SchedRecalc As Date

Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"

End Sub

Sub Recalc()
Range("A50").Value = time

Call SetTime
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

The following code is placed in the sheet:

Private Sub UserForm_Initialize()
Label1 = Sheets("Dagteller").Range("A48").Text
Label2 = Sheets("Dagteller").Range("A49").Text
Label3 = Sheets("Dagteller").Range("A50").Text
End Sub

 

[Wil Moosa]

Select your form and in the Properties select ShowModal=False.

Well, this made the difference... The only problem is that I can not cancel the form!

 

[Jengor]

I have been playing with your code and other submissions. With the form set to showmodal false, I placed the following snippet of code in the form.

Private Sub UserForm_Activate()
    Call UpdateLabel
End Sub

Private Sub UserForm_Deactivate()
    UserForm1.Hide
End Sub

The second routine will of course close the form when the X is clicked

The rest of the code just updates the label of the form that I used.

Sub UpdateLabel()
    UserForm1.Label1.Caption = Range("A1").Text
End Sub

Sub StartIt() 'sole function to start the ball rolling
    UserForm1.Show
    Call Recalc
End Sub

Sub SetTime()
    Dim SchedRecalc As Date
    
    SchedRecalc = Now + TimeValue("00:00:01")
    Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub Recalc()
    Range("A1").Value = Time
    Call UpdateLabel
    Call SetTime
End Sub

HTH

 

[Jengor]

Modifications to the code above to stop the timer as well as the form.

Private Sub UserForm_Activate()
    Call Recalc
End Sub
Private Sub UserForm_Deactivate()
    UserForm1.Hide
End Sub

Sub StartIt()
    UserForm1.Show
    Recalc
End Sub

Sub Recalc()
    Dim SchedRecalc As Date
    
    Range("A1").Value = Time
    UserForm1.Label1.Caption = Range("A1").Text
    If UserForm1.Visible = True Then '
        SchedRecalc = Now + TimeValue("00:00:01")
        Application.OnTime SchedRecalc, "Recalc"
    End If
End Sub

[/code]