MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Paul on December 07, 2001 3:46 PM

i need to create a macro that will run after so many seconds of clicking a button that it is assigned to. Can anyone please tell me a way to do this, or send me the macro code or something?

Posted by Dank on December 07, 2001 4:31 PM


Say you're button is on a worksheet (and it's the button from the Control Toolbox). Double click it and enter this:-

Private Sub CommandButton1_Click()
Application.OnTime Now() + TimeValue("00:00:05"), "MyProc"
End Sub

Then in a module add a procedure called MyProc e.g.

Sub MyProc()
MsgBox "Procedure ran 5 seconds after pressing button."
End Sub


Posted by Guiderius on December 07, 2001 4:50 PM

To pause the macro for 5 seconds after clicking the button, put the following as the first line of the macro :-

Application.Wait Now() + TimeValue("0:0:05")

Note : During the 5 second pause, you will not be able to do anything else with your worksheet.

If you want to be able to input to your worksheet while the macro is paused :-

Dim dNext As Date
Dim x As Integer
Sub Delayed_Start()
dNext = Now + TimeValue("00:00:05")
If x = 0 Then
x = 1
Application.OnTime dNext, "Delayed_Start"
Exit Sub
x = 0


On Error Resume Next
Application.OnTime dNext, "Delayed_Start", schedule:=False
End If
End Sub

Posted by Paul on December 09, 2001 10:52 AM

What i need to do is have a macro that after 5 seconds, unhide a the book i'm working on. Could you please send me the code, and tell me how to put it into the book? I'm kinda new at this.

Posted by Guiderius on December 09, 2001 3:39 PM

Don't understand ........

..... if the workbook is hidden, you can't be working on it. And you can't click a button on a hidden workbook to unhide it until you have unhidden it (Catch 22 !!). You could only do it by having the button either in another workbook or on a toolbar, and have the macro refer to the specific workbook name to be unhidden.

Also, what is the purpose of pausing for 5 seconds ?

Posted by Paul on December 09, 2001 7:42 PM

Re: Don't understand ........

Well, its for a spreadsheet that will be used in excel 97, and whenever you click a hyperlink it hides the worksheet. I was going to assign this macro to each hyperlink, so it will unhide the worksheet. I think it might work if you make one that hides it first, then unhides, even thou it is already hidden.