Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Unanswered: How to handle Msgboxes in other applications

    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.Run "[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

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by SR22Mike
    How do I get VBA to handle these message boxes? the msgboxes are vbYesNo.

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

    vbmEnd = MsgBox("End program?", vbYesNo + vbDefaultButton2, "End Program")
    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.


    ~
    ~

    Bill

  3. #3
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •