Pop up an Excel MsgBox from Access VBA

[Father Branigan]

I have VBA code in Access to open an Excel spreadsheet, modify, etc... That's all working fine.

When I create a MsgBox, the box pops up in the Access window (which is now obscured by the Excel sheet that's now in front). Is there a way to pop up the msgbox in Excel so that the user will see it?

 

[John Big Booty]

You will need to put the portion of the code that opens the message box in your Excel spreadsheet and then trigger it some how when Excel is opened.

 

[Father Branigan]

Hmm, the problem is that the user is always processing new files that they get from another party so I don't think I'll be able to embed code in the Excel sheet. Can I use Access VBA to embed a macro in the Excel sheet?

 

[The_Doc_Man]

You might try forcing a SetFocus on an object in the Access form that is up right at the moment, which might bring it forward. Then your popup would be visible. Unfortunately I'm not sure how to force focus back to Excel when you are done. On the other hand, you might be able to minimize the Excel window and then restore it later.

I'm answering this from home so I don't have my code repository handy from work, but there are some things you could search online for VBA code to manipulate windows - to maximize, minimize, restore, move, etc. They are not that hard if you just copy them into a public module and make the calls public. Then you would be able to do what was needed pretty easily. In fact, search this forum for "Maximize window" and see where that leads. It might be the code I'm thinking about.

 

[Father Branigan]

I have been using the method to minimize Excel, show the msgbox, and then maximize Excel.

excel_app.WindowState = xlMinimized
MsgBox "This Excel file does not fit the standard format."
excel_app.WindowState = xlMaximized

It's workable, but I just figured there has to be a way to distinguish between Access msgboxes and Excel msgboxes when using automation between the two. That is, i figure there must be a "proper" way to do it that's not a hacky workaround.