Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Answer MSGBOX Programatically
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Answer MSGBOX Programatically
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 "kohai" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Answer MSGBOX Programatically
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. "Ronald Dodge" wrote: 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 "kohai" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Answer MSGBOX Programatically
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 "kohai" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Answer MSGBOX Programatically
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 "kohai" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculator Answer Doesn't Match Excel Answer | Excel Discussion (Misc queries) | |||
msgbox answer | Excel Programming | |||
i cant get the exact answer e.g answer is 13.49% i got 13.00% | Excel Discussion (Misc queries) | |||
Follow up from Bob Phillips' Answer on Programatically Making Chec | Excel Programming | |||
Programatically set ref to my DLL | Excel Programming |