Timer: I'm confused

Johnegee

New Member
Joined
Feb 16, 2017
Messages
20
Hello, all,

I have spent perhaps a couple hours searching the Web and trying various suggestions, and I can't seem to figure out how to implement a timer in Excel 365. I know it should be simple but I'm still confused.

The exact implementation I'm hoping for is pretty straightforward. I have written a Tip Generator that uses the random number generator native to Excel to determine whether my tip should be $5 or $10, and to insure true randomness I do use the Randomize function or formula or whatever it is, which I think relies on the exact moment when the clickable grey box is touched on my touch-screen laptop, which fires up a macro that simply generates the random number.

Because so few of the servers understand what to do -- despite my several attempts to simplify it -- I have taken the suggestion to implement a timer. The idea is for a server to touch the grey box that starts a timer that counts down five seconds and then, based on whether the random number is greater than 0.50, paints to the screen the new random number and whether the tip that time is $5 or $10.

What I can't seem to figure out is how, once the timer has counted down from 5 to 0, to get the random number and the resulting tip to paint to the screen and then stop. I suppose the macro could also paint to the screen something along the lines of "The random number you generated is [greater than] [less than] 0.5000, so your tip is [$5] [$10]," but that sort of thing is barely within my capabilities. What's not is how to get something -- anything -- to happen once the timer has gone from 5 to 0.

I assume these processes must be performed with one or more macros, but I'm still confused. Thanks for any ideas you can offer.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
.
VBA Code:
Option Explicit

Sub t()
Dim i As Integer
Dim s As Long
Sheet1.Range("A1").Value = i
For i = 5 To 0 Step -1
     Range("A1") = i
     s = Timer + 1
     Do While Timer < s
        DoEvents
    Loop
Next
End Sub
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Johnegee

New Member
Joined
Feb 16, 2017
Messages
20
JLGWhiz, that link goes to several examples of how to use the timer (written by someone whose native language is not English), and I'm so ignorant that I was, selfishly, hoping someone here would merely supply me with the code. As I say, at one point many days ago I did stumble upon a way to display on the screen the countdown timer as it decremented from 5 to 0, and that's what I'm hoping to reproduce. FWIW, the random number also changed every time the timer dropped by one second but I can live with that.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
JLGWhiz, that link goes to several examples of how to use the timer (written by someone whose native language is not English), and I'm so ignorant that I was, selfishly, hoping someone here would merely supply me with the code. As I say, at one point many days ago I did stumble upon a way to display on the screen the countdown timer as it decremented from 5 to 0, and that's what I'm hoping to reproduce. FWIW, the random number also changed every time the timer dropped by one second but I can live with that.
Click on the big green letters, not the little grey ones. It is all in English and gives VBA examples. There are other websites that give count down and display information with vba code examples if you browse using using the tag line 'vba timer display'

But they are not that much different than what has been posted here already. they just enhance the code by using message box display or displaying in 'hhmmss' format.

I was trying to recall back between 1995 and 2003 I think that there was a clock that could be displayed on the monitor screen and it could also be used as a countdown timer. But that feature has been eliminated from recent versions and the time is just shown on the status bar now.
 
Last edited:

Johnegee

New Member
Joined
Feb 16, 2017
Messages
20
I'm still confused. I've tried what seems like a dozen variations and I still can't get the VBA code to do what I want, which at this point is to start a timer that visibly counts down from 3 seconds to 0 seconds and then generates a random number that determines whether my tip should be $5 or $10. The problem is getting the counter to be visible and start when a server touches "the grey box" on my touch-screen laptop. I've got the rest figured out, but I'm getting frustrated trying to work out how to get the timer to be visible and otherwise work without errors. Right now it gets trapped in an endless loop, but I've generated many other errors.

Will someone please correct my code so as to accomplish what I want. I'll donate $5 or $10 to your favorite charity.

Here's the (defective) code right now.

Option Explicit ' Const idleTime = 3 ' seconds Dim Start Dim i Sub StartTimer() Start = Timer For i = 3 To 0 Step -1 Do While i > 0 Range("D13").Select Loop Next Randomize Calculate End Sub

Help!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

If you want the count down from 3 to 0 displayed then run this code. You will need to add the code to calculate the tip, etc. But this will definitely display the count down. You will also have to adapt it to your touch screen for activation. Sorry I still have the old desktop tower.

If you have other shapes on the sheet, then the 'sh.Shapes(1).Delete' would need some work to avoid deleting other shapes.

VBA Code:
Sub greybox()
Dim sh As Worksheet
Set sh = ActiveSheet
    For i = 3 To 0 Step -1
        Set newWordArt = sh.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1, Text:=i, FontName:="Times New Roman", _
                FontSize:=32, FontBold:=True, FontItalic:=False, Left:=ActiveWindow.UsableWidth / 2 - 200, Top:=10)
        s = Timer + 1
        Do While Timer < s
            DoEvents
        Loop
        sh.Shapes(1).Delete
    Next
End Sub
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
VBA Code:
Option Explicit

Sub t()
Dim i As Integer
Dim s As Long
Sheet1.Range("B5").Value = i
For i = 3 To 0 Step -1
     Range("B5") = i
     s = Timer + 1
     Do While Timer < s
        DoEvents
    Loop
Next
Sheet1.Range("B5").Value = ""
GetRandomWorkout
 
End Sub

Sub GetRandomWorkout()

Dim RNG As Range
Set RNG = Range("A1:A2")

Dim randomCell As Long
    randomCell = Int(Rnd * RNG.Cells.Count) + 1

    With RNG.Cells(randomCell)
            .Copy
            Range("A3").Select
        ActiveSheet.Paste
        MsgBox "$ " & Range("A3"), vbInformation, "Tip Value"
        Application.CutCopyMode = False
    End With

End Sub

Download workbook : Timer.xlsm
 

Johnegee

New Member
Joined
Feb 16, 2017
Messages
20

ADVERTISEMENT

For no good reason I went with part of Logit's code, and in a moment I will start the timer to show whether I should pay Logit $5 or $10. Wait . . .

It came up $5 (the random number at time 0 was 0.2682), so Logit, please tell me where to send the $5. If you do this for a living I'm happy to send it to you; otherwise tell me the name of your favorite charity and I'll send the money there.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
.
No need.

However, if you ever have a need for a full blown project let me know. Then we'll discuss payment.

Glad you have an answer.
 

Johnegee

New Member
Joined
Feb 16, 2017
Messages
20
OK, Logit. I will increase the next tip that's generated by $5, and if I need a full-blown project I will keep you in mind.

Thank you all for your help.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
OK, Logit. I will increase the next tip that's generated by $5, and if I need a full-blown project I will keep you in mind.

Thank you all for your help.
Members are not allowed to accept payment for solutions provided on the forum, and neither are they supposed to offer payment. but a "Thank You" is always welcome and a click on the 'Like' button is also appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,430
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top