Answer MSGBOX Programatically

[Guest]

Hi,

I am writing a macro that opens other workbooks, calls macros in these
workbooks to update data and print some sheets. When all of that is
finished, there is a msgbox funtion that let the user know this process was
completed.

MSGBOX "xxxxxxx",vbokonly,"title"

When these files are open singularly, the msgbox is no big deal, but now I
am processing over a hundred of these files and the pause created by the
msgbox is a problem. Besides going in to each file and removing the msgbox
code, is there any way to programatically answer these from the original
code??

Thanks,

Kohai

 

[Ronald Dodge]

There is no real way to do what you are requesting to do as I have made some
different attempts at this very same issue in the past and didn't have any
success with it. However, are these msgbox codes within either the
Workbook's Open Event or the Auto_Open procedure of a standard module? If
so, you can bypass some of this by following the following set of things to
take into account:

Auto_Open will only run when the workbook is opened by something other than
within VBA or if it's called on by using VBA's Application.Run method on the
Auto_Open procedure

Workbook's Open event is suppose to run regardless if it's opened by way of
macro or by way of user, but in our experience, this worked for a while, but
then something happened and now this event doesn't work for us anymore,
which also has caused problems with causing the Application.EnableEvents to
be set to False, a side effect that no one likes cause of what we use at the
plant.

I am not saying you will have this side effect, especially given the fact
that I'm not even sure what caused it to start out with, but it's something
to think about. At this point though, I think you using the Auto_Open
procedure would be your best bet so as you can make the necessary
adjustments as needed, though if you have plenty of different workbooks out
there like we do with our DERF program, you may have to turn to using
versions, which is what we have done, which then force all users to use the
current version. Of course, include a grace period, which may be from 30
days to 90 days.

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

Ronald,

Thanks for the feedback. The procedures within the workbooks are neither
Auto_Open or in the Workbook's Open event. It runs by command button. This
was done since the user may want to look at or use the file many times before
the new data is available (usually weekly) and thereby not waiting each time
the file is open to get the data they already have.

It appears that under the current situation, I'm either going to have to
click ok on each file's msgbox, or go into each file and remove the msgbox
code.

Unless anyone else out there knows how to do this.

Thanks.

 

[Jim Rech]

You might consider a self-closing msgbox, at least, next time.

Sub SelfClosingMsgBox()
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Popup "Hello!", 2, "This closes itself in 2 seconds"
End Sub

 

[Dave Peterson]

I just saw this on Lifehacker.com:

http://lifehacker.com/software/excl...-timed-mouse-clicks-with-clickwhen-260445.php

or

http://snipurl.com/1kr89

I didn't download it (or try it).

 

[Peter T]

Hi Kohai,

This seems to work for me -

Put the routines in two separate projects, change "Book2" to the name of the
workbook containing "SomeProcess".

' code in Book1
Sub test()
Dim i As Long, LastStep As Long

LastStep = 5

For i = 1 To LastStep

If i < LastStep Then
Call Application.SendKeys("{ESC}")
End If

Application.Run "Book2!SomeProcess"
Next
End Sub

' code in a normal module in Book2

Sub SomeProcess()

MsgBox "Process done"
Debug.Print "SomeProcess has run"
End Sub

Regards,
Peter T

 

[Guest]

Thanks to all of you for your suggestions. I wasn't aware of the
self-closing msgbox and that is cool thing to know.

Kohai