Showing a Msgbox in the active application
 

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

--
Message posted from http://www.ExcelForum.com

Showing a Msgbox in the active application
 

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 " wrote in message
...
Hi,

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

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

However I cant seem to get any msgbox messages to show up in the excel
application. Instead they always appear within MS Project and the only
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, not
the application where the macro originated?

I hope someone can help.

thanks,

Matt


---
Message posted from http://www.ExcelForum.com/

Showing a Msgbox in the active application
 

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

--
Message posted from http://www.ExcelForum.com

Showing a Msgbox in the active application
 

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 " wrote in message
...
Thanks for the reply. However, I had already tried your suggestion
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. The
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,

Matt


---
Message posted from http://www.ExcelForum.com/

Showing a Msgbox in the active application
 

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

--
Message posted from http://www.ExcelForum.com

Showing a Msgbox in the active application
 

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)

"donovanm " wrote in message
...
Ok, thanks Bob.

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

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

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

thanks,

Matt


---
Message posted from http://www.ExcelForum.com/

Showing a Msgbox in the active application
 

"Bob Phillips" wrote ...

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 he

http://vbnet.mvps.org/index.html?cod...xhooktimer.htm

suggests you can specify the owner window for a messagebox.

Jamie.

--

Showing a Msgbox in the active application
 

Ok, this looks like I'm getting somewhere!

Thanks for your help!

Mat

--
Message posted from http://www.ExcelForum.com