Missing somthing - OnTime?

[jim may]

In my ThisWorkbook:

Public OpTime As Date

Private Sub Workbook_Open()
OpTime = Now()
Application.OnTime OpTime + TimeValue("0:0:05"), "MyMacro"
End Sub

In Standard Module:

Sub MyMacro()
MsgBox "You opened this workbook at " & OpTime
End Sub

So why does the Msgbox Show ONLY:
You opened this workbook at

Stepping thru I can see in the Standard Module that OpTime IS EMPTY
But why?

Jim

 

[VoG]

Hi Jim. Try moving

Public OpTime As Date

to a standard module.

 

[Norie]

The code being called by the should set the next ontime.

Sub MyMacro()
    MsgBox "You opened this workbook at " & OpTime
    Application.OnTime Now() + TimeValue("0:0:05"), "MyMacro"
End Sub

 

[jim may]

The Scope thing has always confused me. Might I conclude... from your statement that ...?

Use of the Public keyword should be limited to Standard Modules, and never ThisWorkbook module. or ????

Always aprreciate your help. Jim

 

[Norie]

Oops, ignore post.

Thought you wanted the code to run repeatedly.

 

[VoG]

The code in the ThisWorkbook module can "see" variables declared in a standard module but not vice versa.

 

[jim may]

Norie, making the change recommended by Peter makes things work.

Minutes before I saw code similar to yours, but didn't grasp it, as I quickly noticed that a Macro named MyMacro was INSIDE the same Macro named MyMacro. That confused me, so I avoided it. And actually, your code confuses me more... But thanks Jim

 

[VoG]

This is a quite good description of how to use the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[jim may]

The code in the ThisWorkbook module can "see" variables declared in a standard module but not vice versa.

Peter, I LOVE such RULES. They seem to be hidden, thus the need for "prospecting for gold"...

Again, Tks

Jim

 

[Norie]

Jim

What is it you wanted to do?

Run the code once, 5 seconds after the workbook opened?