Showing a Msgbox in the active application

[donovanm]

Hi,

I'm hoping this is an easy one! I've spent some time searching for a
answer on this forum but to no avail.

I have a macro that is run from MS Project. It opens and activates a
excel workbook and copies some information from it.

However I cant seem to get any msgbox messages to show up in the exce
application. Instead they always appear within MS Project and the onl
way to view it is to manaully switch back to MS Project.

How can I make sure that msgbox's appear in the active application, no
the application where the macro originated?

I hope someone can help.

thanks,

Mat

 

[Bob Phillips]

Qualify the MsgBox command with the Excel object

xlApp.MsgBox etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[donovanm]

Thanks for the reply. However, I had already tried your suggestio
thinking it would work, but it doesnt seem to!

Here is a snippet from my code

Set xlApp = CreateObject("Excel.Application")

xlApp.workbooks.Open sMilestonePathFileName

xlApp.Visible = True

xlApp.worksheets(sPPRMilestoneSheetName).Select

xlApp.MsgBox "Hello"


Lines 1 to 4 run fine, but the code fails when it reaches line 5. Th
error message I get is as follows:

Run-time error '438':
Object doesnt support this property or method.

Any suggestions will be greatly appreicated!

thanks,

Mat

 

[Bob Phillips]

You could create macros in the workbook to open and run those (hard work?)
or try this

xlApp.ExecuteExcel4Macro "alert(""Hello"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[donovanm]

Ok, thanks Bob.

xlApp.ExecuteExcel4Macro "alert(""Hello"")"

Your line does work. However I assume I wont be able to create
Yes/No/Cancel msgbox using this type of approach?

Could you tell me (or point me in the direction of some info) on how
can run an Excel macro contained in a workbook from MS project/MS Wor
etc? You say it is hard work, but I think in my case it would save m
some problems.

thanks,

Mat

 

[Bob Phillips]

Matt,

The actual call is quite simple

xlApp.Run Activeworkbook.Name & "!MacroName"

The hard work comes in setting up a macro in that workbook that displays a
message box, as it will need to be in all target workbooks, and reduces your
code flexibility.

Also, as your seemed concerned to have MsgBox with Yes No buttons, you will
need to create a function in the workbook(s), and return the resul, and test
that in your original code, like

Function myFunc()

myFunc = MsgBox("Hello", vbYesNo)

End Function

in the workbook(s), and call with


ans = xlapp.Run(ActiveWorkbook.Name & "!myFunc")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jamie Collins]

...

The hard work comes in setting up a macro in that workbook that displays a
message box, as it will need to be in all target workbooks, and reduces your
code flexibility.

If you are not afraid of hard work you could investigate the
possibility of using the Win32 API MessageBox. A brief look at the
code here:

http://vbnet.mvps.org/index.html?code/hooks/messageboxhooktimer.htm

suggests you can specify the owner window for a messagebox.

Jamie.

--

 

[donovanm]

Ok, this looks like I'm getting somewhere!

Thanks for your help!

Mat