Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed with msgbox
Hi NG.
I have a litte problem with my Macro. I would like to show a message-box with vbyesno-buttons. This msgbox should stay open for about 5-10sec and then close automatically. In the msgbox I ask the user if he wants to open an Excel-file, and he can answer "yes" or "no", as expected :) But at the msgbox the macro stops and waits until the user clicks a button. Now I don't want the macro to wait for eternity and go on after the specified time. If the user didn't click a button in this time the msgbox-result should be set to "yes" and the box should close. I think an application.wait is not the right way, because excel waits before the msg is shown or the msg appears before the wait command is active. Or do I have to create a UserForm? Has anyone an idea how to solve this? Thanks in advance. MB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed with msgbox
Hi MB,
Here is an example of a timed msgbox. Dim cTime As Long Dim WSH As Object Set WSH = CreateObject("WScript.Shell") cTime = 10 ' 10 secs Select Case WSH.Popup("Open an Excel file?!", cTime, "Question", vbOKCancel) Case vbOK MsgBox "You clicked OK" Case vbCancel MsgBox "You clicked Cancel" Case -1 MsgBox "Timed out" Case Else End Select As you can see, it can be OK, Cancel or timed out. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Marcus Bischoff (AnW/P-AB9)" wrote in message ... Hi NG. I have a litte problem with my Macro. I would like to show a message-box with vbyesno-buttons. This msgbox should stay open for about 5-10sec and then close automatically. In the msgbox I ask the user if he wants to open an Excel-file, and he can answer "yes" or "no", as expected :) But at the msgbox the macro stops and waits until the user clicks a button. Now I don't want the macro to wait for eternity and go on after the specified time. If the user didn't click a button in this time the msgbox-result should be set to "yes" and the box should close. I think an application.wait is not the right way, because excel waits before the msg is shown or the msg appears before the wait command is active. Or do I have to create a UserForm? Has anyone an idea how to solve this? Thanks in advance. MB |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed with msgbox
works fine. Thanks a lot.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed with msgbox
Marcus,
As you rigthly point out, it won't work with a Messagebox. The code stops running until a button is pressed. Also, using Application.Wait or Do While loops with timers dont work as they wait for the time to pass, before giving control back. So what I would do is the following: 1. Make a custom Userform with a "Yes" and "No" button. 2. In the UserForm_Activate put the following code: Call OnTimeStart 3. In the code for the Yes and No buttons, put the following code, before the code you want to run when Yes or No is pressed: Call OnTimeStop 4. In a module, declare a Public Variable ExecTime: Public ExecTime As Date [Don't forget ;-)] 5. In the module put the following code: Sub OnTimeStart() ExecTime = Now + TimeSerial(0, 0, 10) ' 10 is your delay in seconds Application.OnTime ExecTime, "TestA", , True MsgBox ("Time pressed: " & Format(Now, "hh:mm:ss") & " - Scheduled: " & Format(ExecTime, "hh:mm:ss")) End Sub Sub OnTimeStop() Application.OnTime ExecTime, "TestA", , False MsgBox ("Stopped at :" & Format(Now, "hh:mm:ss") & " - Scheduled: " & Format(ExecTime, "hh:mm:ss")) End Sub Sub TestA() MsgBox ("Ontime Prcedure Called after elapsed time") 'Put your code to open the file 'Also add code to unload the userform (Unload "UserFormName") End Sub So what happens: - When your Yes-No-UserForm is activated it calls the "OnTimeStart" sub, which schedules the "TestA" procedure to start 10 seconds after Now - If the user does not press the yes or no button within 10 seconds, the TestA procedure (for example to open the file) is started and the userform is unloaded - If the user does press a button, the "OnTimeStop" procedure is called, which cancels the previously set schedule to run "TestA" after 10 seconds and runs whatever code you put in the commandbutton_click Hope this works for you. Good Luck ! "Marcus Bischoff (AnW/P-AB9)" wrote in message ... Hi NG. I have a litte problem with my Macro. I would like to show a message-box with vbyesno-buttons. This msgbox should stay open for about 5-10sec and then close automatically. In the msgbox I ask the user if he wants to open an Excel-file, and he can answer "yes" or "no", as expected :) But at the msgbox the macro stops and waits until the user clicks a button. Now I don't want the macro to wait for eternity and go on after the specified time. If the user didn't click a button in this time the msgbox-result should be set to "yes" and the box should close. I think an application.wait is not the right way, because excel waits before the msg is shown or the msg appears before the wait command is active. Or do I have to create a UserForm? Has anyone an idea how to solve this? Thanks in advance. MB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MsgBox | Excel Discussion (Misc queries) | |||
Msgbox | Excel Worksheet Functions | |||
Help with MsgBox... | Excel Worksheet Functions | |||
MsgBox | Excel Programming | |||
MsgBox | Excel Programming |