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
