Application.OnTime can't see my macro

[mrMadCat]

I have this code in Workbook module:

Sub MESSAGE1()
msgBox "Change"
End Sub

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime Now + TimeValue("00:00:02"), "MESSAGE1"
End Sub

After 2 seconds it says that there is no such macro Message1. What did I miss? Thank you.

 

[GTO]

Edit... hold on a second...I am blinder than usual.

 

[GTO]

Okay, not tried, but as I am reading it, it appears that you have MESSAGE1() in the ThisWorkbook Module, is that correct?

 

[mrMadCat]

Yes, it is in ThisWorkbook Module. I found a solution

Application.OnTime Now + TimeValue("00:00:02"), "ThisWorkbook.MESSAGE1"

But I'm using Russian version of excel and ThisWorkbook is called ЭтаКнига, so I have to write "ЭтаКнига.MESSAGE1".
I'm afraid it won't work if I run this file on any other version than Russian.

 

[GTO]

I do not know that opening an already created workbook in another version will change any names. I would suspect not.

That said, whilst prefacing the procedure name with the module name is fine, the issue that I would suggest looking at is where the called procedure is. That is, it is just simply easier to keep a procedure called by .Run, .OnAction or .OnTime housed in a Standard Module. Move MESSAGE1() to a Standard Module and you should have no issues

Hope that helps,

Mark

 

[mrMadCat]

I thing I tried that before with no luck, but after your advice I tried it again and it worked ) Thank you!

 

[mrMadCat]

Dear Mark, I would also be grateful if you could say how I can in this macro stop all previous Application.OnTime. I mean reset timer first every time on event Workbook_SheetSelectionChange.
I'm reading this now in different places in the Internet but I'm becoming more and more confused.

 

[GTO]

I am not exactly sure what you are asking, but here is a link to see how to store the EarliestTime, which is the key to cancelling a previously scheduled call.

OnTime: Automatically Run Macros at a Set Time or Date. Application.OnTime

 

[mrMadCat]

Yes, this is what I need, I've read it in many times and tried to adapt to my situation but it is not working. Probably did something wrong but can't understand what. ( I created a separate question for this:
http://www.mrexcel.com/forum/excel-questions/946007-macro-reseting-ontime-every-event.html