Excel VBA: How to use Application.ontime to call a click event?

Question

I'd like to use the Application.ontime method to call a button_click event on my Excel form.

when i use this code I can call a sub located in my Module1:

Application.OnTime Now + TimeValue("00:00:15"), "test"

However if I use this code it doesn't call my Private Sub CommandButton1_Click (it does nothing):

Application.OnTime Now + TimeValue("00:00:15"), "CommandButton1_Click"

How can I call a click on my button "CommandButton1"

Thanks for your help!

score 1 · Accepted Answer · answered Jun 15 '16 at 18:47

1

You need to expose the desired functionality. In a standard module enter, for example:

Public Sub ReallyVisibleMacro()
    MsgBox "Hello World"
End Sub

This sub does all the real work. Then your button code would be:

Private Sub CommandButton1_Click()
    Call ReallyVisibleMacro
End Sub

and in the OnTime code:

Application.OnTime Now + TimeValue("00:00:15"), "ReallyVisibleMacro"

answered Jun 15 '16 at 18:47

Gary's Student

:Thanks for the answer. I thought about something like that but I was trying to avoid to transfer the code from my Sub CommandButton1_Click to a standard module. When I do that I'm getting an error 91 which I'm not getting when my code is not in the module. I was hoping that I could simply call my CommanButton1_Click sub... – sims Jun 15 '16 at 19:44
@sims You might need to possibly modify the button code to avoid using button-specific thing like `Me` – Gary's Student Jun 15 '16 at 19:47
Thanks Gary. I did make the correction to get rid of the Me's but I'm still getting an error 91. Your answer works though. Thanks for your help. – sims Jun 15 '16 at 20:58
1

It finally worked for me. I placed all the code that was in the CommandButton1_click in a the same usrform1 as a sub called "finished". OnTime called a sub in my Module1 which called my sub in my form by using usrform1.finished. I know it's not elegant but it works for me. Your help is very much appreciated! – sims Jun 16 '16 at 01:04

1 Answers1