msg box - automatic click o.k.

boxsterman

Active Member
Joined
Apr 16, 2002
Messages
279
Hi all,

I have a macro which runs another macro several times to fill down information. The problem is that the original has a msg box at the end to inform the user that the task is complete.

Is there a way to have my macro select o.k in the msg box, without any user interfereance?

thx.

J
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Couldn't you just remove the line of code for the msgbox? If you're automatically going to click OK, why have it there at all?
 

boxsterman

Active Member
Joined
Apr 16, 2002
Messages
279
The original macro is for the end user, to notify them of the change.

I'm leveraging off the existing code, to run some stuff in the background. I know there are several ways around this, I was just curious if I could write some code to click ok.

J.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Build a Calling Macro that calls each of the needed macros and as noted above remove the last MsgBox from that MsgBox code and place it in it's own Sub.

If you need the code to run two different ways than build two Calling Sub's one to run one way pulling each of the needed Subs and another to do it differently. Then replace whatever it triggering your Subs with the new Calling Subs!
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
OK just for info, Yes you can do it, here is a test Sub to show you how:

Note: This does not use the Excel MsgBox, it uses the Windows Scripting MsgBox which looks the same but works differently. This box allows a auto responce where the Excel MsgBox does not [Yes and No there is a nasty Class code to do it, but it is real limited.] This Script MsgBox works great and it is easy!

Sub myTimeBox()
'Sheet module code, like: Sheet1.
'Close message after time if no action!
Dim myTimedBox As Object
Dim boxTime%, myExpired%, myOK%, myQuestBox%

'Access timed message box.
Set myTimedBox = CreateObject("WScript.Shell")
boxTime = 5

'Get user responce!
myQuestBox = myTimedBox.Popup("Click OK!" & vbCr & vbCr & "Or," & vbCr & _
vbCr & "Do nothing and this message will close in 5 seconds.", _
boxTime, "Select ""OK"" to Continue!", vbOKOnly)

'User Selected "OK."
If myQuestBox = 1 Then
'Add any code in place of code below for this condition!
myOK = myTimedBox.Popup("You Clicked OK!" & vbCr & vbCr & "Or," & vbCr & _
vbCr & "Do nothing and this message will close in 5 seconds.", _
boxTime, "You Took Action!", 0)

Else

'User took no Action!
myExpired = myTimedBox.Popup("You Did Not Click OK!" & vbCr & vbCr & "Or," & vbCr & _
vbCr & "Do nothing and this message will close in 5 seconds.", _
boxTime, "No Action Taken!", 0)
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,099
Members
412,441
Latest member
kelethymos
Top