Hi,

Okay, I have three excel spreadsheets.

Via a macro. The first spreadsheet generates the second spreadsheet.

The third spreadsheet, via it's own macro, copies the data of the second spreadsheet into itself.

At present, I have added the following code to the first spreadsheet:

Workbooks.Open Filename:= "[the third spreadsheet].xls"
Application****n "[the third spreadsheet].xls'!Update"
ActiveWorkbook.Save
ActiveWindow.Close

However, when I wrote the code for the Update macro in the third spreadsheet I included a couple of message boxes. How do I get VBA to handle these message boxes? the msgboxes are vbYesNo.

I've thought up a few ways, but they're complex and I am wondering if there is a far easier solution.

For Reference, the code for these message boxes are:

vbmVisible = MsgBox("Make Visible?", vbYesNo, "Tabs Visible")

vbmEnd = MsgBox("End program?", vbYesNo + vbDefaultButton2, "End Program")

Thanks,
Mike

I'm not understanding what problem your having with the message box. Generally you would only display a Yes/No message box for user interacation. So to have VBA handle the decision you should not use a message box at all.

My approach would be to remove all the code from the three workbooks and put all the code in a single control workbook, code only that opens the other three books and performs the actions by referring to them using 'Activeworkbook' or Workbooks("BookName.xls"). Your control code workbook can be hidden from view so it is transparent during the operation. Using a message box in the code book will display on the active workbook.


~

Thanks for the suggestion, but it is not an option for me to combine all of these spreadsheets into one spreadsheet. They all have their own reasons for being the way they are.

What my issue is, that I would like to have VBA handle the message boxes when they appear as a part of running another application's code. I'm guessing that I will need to dimension a Public Boolean variable and write code just before the message boxes on whether or not to run the code for the message boxes. But I am hoping that someone else would have an better idea than this.

Thanks,
Mike