Getting MsgBox to show up in Excel (1 Viewer)

[gypsyjoe11]

Hi everyone,

I wrote an Access 2003 function that takes in either a table or select query name. If you give the function a file path it will save it to disk. If you leave the file path out it opens it up in a new Excel application without saving it. You can also set booleans for visiblity and wether to overight the file.

If my function is saving it to disk and the overight is false, I do a check to see if the file exists. If it does I bring up a msgbox asking wether they want to overight the file.

The problem is this msgbox shows up behind the open Excel application and I have to bring up Access in order to view the message.

I have tryied various ways of selecting active workbooks, sheets and cells in the Excel application before bringing up my msgbox but nothing works.

Any ideas would be greatly appreciated,

Joe

 

[Access_guy49]

Example:
MsgBox "hello", vbMsgBoxSetForeground

 

[gypsyjoe11]

Access_guy49,

I have a vbYesNo right now. If I change it to vbMsgBoxSetForeground they only have the option to press "ok". It also switches it to Access before displaying the msgbox which is not what I want. I want it to show up on top of the open Excel sheet.

Right now I have:

Dim DisplayBox As VbMsgBoxResult
DisplayBox = MsgBox("File " + filePath + " already exists." _
+ vbNewLine + "Overwright File?", vbYesNo, "Error")
If DisplayBox = vbNo Then
'close objects and exit
End If

Thanks for your advice,

Joe

 

[SOS]

Joe:

You can have multiple items for a message box. For example:

DisplayBox = MsgBox("File " + filePath + " already exists." _
+ vbNewLine + "Overwright File?", vbYesNo + vbMsgBoxSetForeground + vbExclamation + vbDefaultButton1 , "Error")

 

[gypsyjoe11]

Thanks SOS,

Do you know if there's a way to get the window to show up over the open Excel sheet?

 

[SOS]

You could try setting focus to the open item.

 

[gypsyjoe11]

I've tried activating the sheet and also selecting a cell before I bring up the box. I'm not sure how to set the focus on the sheet.

 

[Brianwarnock]

As I have no experience here this is a shot in the dark, as i see it you are working in Access therefore the msgbox is relating to that application and when you open Excel over it the best that the masbox can achieve is to be the jam in the sandwich so to speak, could you minimize the excel application before issuing the msg?

Brian

Please ignore if this is rubbish

 

[Access_guy49]

The message box is tied to the application which is running it. hence visual basics for applications (vba)
If you wish to run the message box in Excel then the messagebox must come from the Excel object.

 

[Brianwarnock]

The message box is tied to the application which is running it. hence visual basics for applications (vba)
.

I think that is what I said, thank you for the confirmation.

Brian

 

[Access_guy49]

I did think of a way to do it.
Similar to Editing an Existing Excel file from within Access...

You will need to create an Existing excel file to try this out, but i think you should be able to apply this principal to the "normal.xls"

Basically you create a Macro inside Excel, (if created in the Personal then it will apply to all excel files you open)
You open the file with access and then you run your macro at the end.
Make sure the Macro is in it's own Module.
I tried this and it worked just fine..
Open Excel, Create a blank project and just go up an run the macro recorder for a second. (i do this just to make sure it's set up properly) Then alt+F11 to open VBA.
Go to the modules and view your macro code. Change the code to something like

 msgbox "Hello"

Your Macro by default will be called Macro1.

Use this code in your Access Form on a button (or whatever you want)

    Dim oXL As Object
    Dim NameOfFile As String

    NameOfFile = "C:\test.xls"

    Set oXL = CreateObject("Excel.Application")

    With oXL
        .Visible = True
        .Workbooks.Open (NameOfFile)
    End With

    oXL.Run ("Macro1")

set oxl = nothing