Timer Events

[kevin.philips]

Hi

Having read various posts about creating timer events - I'd thought I'd have a go at something that would resemble a live 'tips of the day board'.

I want to have a userform displayed with 1 text box that generates a random message from the range("A1:A10") on my worksheet. I want this to update say every 20 seconds, much the same as a web banner rotation say.

Can u help

My Code

Private Sub UserForm_Initialize()
msgticker
Application.OnTime Time + ("00:00:10"), "msgticker", True

End Sub
Sub msgticker()
Dim MyRndRange
MyRndRange = Int((10 * Rnd) + 1)
TextBox1 = Sheets("Sheet1").Range("A" & MyRndRange).Text
End Sub

Thanks in advance

Kevin

 

[tusharm]

So, what's the problem/question?

 

[kevin.philips]

Doh,

That small little thing that I forgot......what am I asking.....???

The code does not run

I get the type mismatch error on:

Application.OnTime Time + ("00:00:10"), "msgticker", True

Any Ideas

Regards

Kevin

 

[Haluk]

Hi Kevin;

Try the following,

Insert the below lines to UserForm1 module,

Private Sub UserForm_Initialize()
msgticker
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.OnTime Now + TimeValue("00:00:10"), "msgticker", , False
End Sub

And, insert the following lines to a new standard code module,

Sub msgticker()
Dim MyRndRange
MyRndRange = Int((10 * Rnd) + 1)
UserForm1.TextBox1.Text = Sheets("Sheet1").Range("A" & MyRndRange).Text
Application.OnTime Now + TimeValue("00:00:10"), "msgticker", , True
End Sub

Change the names of the UserForm and TextBox objects (UserForm1, TextBox1) according to your project.

 

[kevin.philips]

Cool, its got the makings of the effects I am thinking of but now my screen flickers even after the macro seems to have stopped and I also notice that it works for a couple of times and then changes the interval - I had random messages every second or so after this.?????

Regards

Kevin

 

[Haluk]

Hi Kevin;

Nothing seems wrong with the code i've offered to you.

According to the above code, in order not to have flickering screen after the execution of the code is stopped (which should be done by closing the UserForm itself, according to the above code lines), the code in the UserForm_QueryClose event disables the OnTime procedure.

So, i guess somehow you've tried to stop the execution of the OnTime procedure without setting it's schedule property to false. This is the point of your problem. Because, without sending the schedule property to false, Excel (the application itself) will be forced to continue executing the macro it has been told to run.

 

[tusharm]

I don't know wht is causing the OP's problems, but...

Hi Kevin;

Nothing seems wrong with the code i've offered to you.

Yes, there is.

...the code in the UserForm_QueryClose event disables the OnTime procedure.

No, it doesn't. To unschedule a previously scheduled procedure, you need the scheduled time. Using Now()+10 seconds will unschedule a nonexisting event.

Code on this subject has been posted often. Search this site (if the search engine works) or the google.com archives of the XL newsgroups. Basically, you need a global variable that holds the time of the next scheduled event.

 

[Haluk]

Hi again;

Taking care of what tusharm has pointed above (which is a correct comment),

For Userform1 module,

Private Sub UserForm_Initialize()
msgticker
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.OnTime RunWhen, "msgticker", , False
End Sub

For the standard code module,

Public RunWhen

Sub msgticker()
Dim MyRndRange
RunWhen = Now + TimeValue("00:00:10")
MyRndRange = Int((10 * Rnd) + 1)
UserForm1.TextBox1.Text = Sheets("Sheet1").Range("A" & MyRndRange).Text
Application.OnTime RunWhen, "msgticker", , True
End Sub