How to automatically run macro when open workbook

[Parkerpen13]

I have a clock that works off from auto refresh and works great but I have to start it off from a button. How would I change my code to automatically run when I open the workbook so I can eliminate the button?

[FONT=&quot]Dim SchedRecalc As Date[/FONT]
[FONT=&quot]Sub Recalc()[/FONT]
[FONT=&quot]With Sheet1.Range("A1")[/FONT]
[FONT=&quot].Value = Format(Time, "hh:mm:ss AM/PM")[/FONT]
[FONT=&quot]End With[/FONT]
[FONT=&quot]Call SetTime[/FONT]
[FONT=&quot]End Sub [/FONT]
[FONT=&quot]Sub SetTime()[/FONT]
[FONT=&quot]SchedRecalc = Now + TimeValue("00:00:01")[/FONT]
[FONT=&quot]Application.OnTime SchedRecalc, "Recalc"[/FONT]
[FONT=&quot]End Sub [/FONT]
[FONT=&quot]Sub Disable()[/FONT]
[FONT=&quot]On Error Resume Next[/FONT]
[FONT=&quot]Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False[/FONT]
[FONT=&quot]End Sub[/FONT]

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
Cick on ThisWorkbook
Paste the code in the VBA edit window

You will have to save and close your workbook and then next time you open this workbook the script will run

Private Sub Workbook_Open()
Dim SchedRecalc As Date
Sub Recalc()
With Sheet1.Range("A1")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
End Sub

 

[Parkerpen13]

Thanks for the reply but it doesn't like that code. This is what I have now.

Private Sub Workbook_Open()
Dim SchedRecalc As Date
Sub Recalc()
With Sheet1.Range("A1")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
End Sub

 

[My Aswer Is This]

Thanks for the reply but it doesn't like that code. This is what I have now.

Private Sub Workbook_Open()
Dim SchedRecalc As Date
Sub Recalc()
With Sheet1.Range("A1")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
End Sub

Glad you have it working.

 

[Parkerpen13]

So here is my code I have and I am getting a Compile error: Expected End Sub and it highlights the top row yellow. Please help I have this in the This workbook.

Private Sub Workbook_Open()
Dim SchedRecalc As Date
Sub Recalc()
With Sheet28.Range("G5")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:01:00")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
End Sub

 

[My Aswer Is This]

I'm really not able to help you because you have several different sub routines running here which I'm not sure what there doing.

Like

Call SetTime​

You have this running twice..

Try removing

Call SetTime​

Your calling it and then running it again.

And it appears all this is doing is letting you know how long it took to run the script.