message box without the "OK" button

[fry]

Hi All

I'm using the following code to try and get a popup window to give the user a warning that the workbook is about to shut down but I'm getting an "OK" button to be pressed.

Is there a setting that will just popup a window rather than you having to press a button...............and can the window size be specified???

Thanks for any help in advance

Application.OnTime Now + TimeValue("00:00:20"), "close_workbook"
MsgBox "This Order Book will self destruct in 15 seconds...", vbExclamation, "User Notice"

Sub close_workbook()
Application.Quit
End Sub

 

[jindon]

try

Application.OnTime Now + TimeValue("00:00:20"), "close_workbook"
CreateObject("WScript.Shell").popup "This Order Book will self destruct in 15 _
         seconds...", 18, "User Notice", vbOKOnly+vbExclamation

Sub close_workbook()
Application.Quit
End Sub

 

[Richard Schollar]

I don't think that solves the problem of having to click on the OK button as the popup is modal? It seemed to be waiting for me to click OK before it did anything.

Another option would be to create a userform for the purpose which you could obviously resize and have no buttons on it.

 

[fry]

Thanks for your replies guys and sorry to say this but I've never used a userform before how would I do that???

Would I therefore be correct in thinking that there isn't a way of "not having" a button on the messagebox?

 

[jindon]

Do you really want to wait 20 sec anyway?
Otherwise

Dim x
x = CreateObject("WScript.Shell").popup "This Order Book will self destruct in 15 _
         seconds...", 18, "User Notice", vbOKOnly+vbExclamation
If x = 1 Or x = -1 Then close_workbook

Sub close_workbook()
Application.Quit
End Sub

 

[fry]

Hi jindon

I tried the above but got the following...

"Compile error: Syntax error" with the following code highlighted

x = CreateObject("WScript.Shell").popup "This Order Book will self destruct in 15 _
         seconds...", 18, "User Notice", vbOKOnly+vbExclamation

 

[Richard Schollar]

Just navigate to your workbook in the VBE and right-click and go Insert>Userform. This will create your userform - drag a label onto it from the Controls Toolbox which should appear alongside the Userform and write your message in it (eg "This workbook will close in 15 seconds" or whatever).

Then use code very similar to what you already have to call the userform (in a modeless state):

Sub test()
Application.OnTime Now + TimeValue("00:00:15"), "closeths"
UserForm1.Show vbModeless
End Sub
Sub CloseThs()
ThisWorkbook.Close
End Sub

 

[Richard Schollar]

Just some missing parentheses re Jindon's code:

x = CreateObject("WScript.Shell").popup("This Order Book will self destruct in 15" & _
         "seconds...", 18, "User Notice", vbOKOnly + vbExclamation)

Still don't think that does exactly what you want though - but the userform route does.

 

[jindon]

Ahh
Thanks Richard...

 

[fry]

Thanks Richard and Jindon it actually does work. Even though there is an "OK" button it shuts down!!

My sincere thanks...