OzGrid

How to set a countdown timer to beep under 10 seconds

< Back to Search results

 Category: [Excel]  Demo Available 

How to set a countdown timer to beep under 10 seconds

 

Requirement:

 

The user has  created a timer in excel and would like it to beep every second under ten seconds. The user has tried the follow method but the beep is happening every second instead of only playing under ten seconds.

 

VBA module:
Function BeepMe() As String
Beep
BeepMe = ""
End Function

In Sheet2 Cell B2 the user has the timer 12:01:30 AM formatted to display mm:ss

In cell C2 the user has this function =IF(B2<"12:00:10",BeepMe()."")

 

Please advise how to only play the beep under 10 seconds.

 

Solution:

 

this formula:

=IF(B2<TIMEVALUE("00:00:10"),BeepMe(),"")

 

OR

 

assuming you have timer in Cell B2 running the below code will work

Code:
Function BeepMe() As String
tim = Range("B2").Value

Do Until Range("B2").Value = (tim + TimeValue("00:00:10"))
DoEvents
Loop
Beep
BeepMe = ""


End Function

Obtained from the OzGrid Help Forum.

Solution provided by John_w and ashu1990

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to use code to insert below and copy the row as many times as the cell contains
How to find and insert rows for missing timestamps, remove rows for unwanted timestamps
How to create VBA code for a workbook to work on week days only and specific range of time
How to create VBA code to increment number each time a copy is printed

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)