CODEWITHSUNDEEP
vbaexceltimer
Marco Soto
Marco Soto

Reputation: 61

How do you create an automatic timer using Excel 2016 VBA macro editor?

I need to create a timer in Excel 2016 VBA editor that will go-off every second and display a message.

I have a simple code that will do just that; however when I run the code a message box appears and then an error follows. My code is presented below:

Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub

Sub RunEveryTwoMinutes()
MsgBox ("Stop!")
Application.OnTime Now + TimeValue("00:00:01"), "RunEveryTwoMinutes"
End Sub

The error I receive states: "Cannot run the macro 'C:user|generic\Book1.xlsm'. The macro may not be available in this workbook or all macros may be disabled.

I am not sure what is occurring. I simply need to find out a way to create a one second timer that initiates as soon as the code initializes.

Upvotes: 1

Views: 1869

Answers (1)

YowE3K
YowE3K

Reputation: 23994

Place this code in your workbook code module:

Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub

and place this code in a standard code module:

Sub RunEveryTwoMinutes()
MsgBox ("Stop!")
Application.OnTime Now + TimeValue("00:00:01"), "RunEveryTwoMinutes"
End Sub

Alternatively, you can have all the code in your Workbook code module, but you need to qualify the macro name:

Private Sub Workbook_Open()
RunEveryTwoMinutes
End Sub

Sub RunEveryTwoMinutes()
MsgBox ("Stop!")
Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.RunEveryTwoMinutes"
End Sub

I believe the first method is preferable.

Upvotes: 1

Related Questions