# Timer: I'm confused

#### Johnegee

##### New Member
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
.
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``````

### 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
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
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
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

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
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``````

#### Johnegee

##### New Member

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
.
No need.

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

#### Johnegee

##### New Member
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
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.

Replies
7
Views
240
Replies
5
Views
224
Replies
2
Views
243
Legacy 456155
L
Replies
4
Views
188
Replies
1
Views
219

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.

### Which adblocker are you using?

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

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