Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Determine if Application Window Still Active | Excel Discussion (Misc queries) | |||
VBA: Make Excel the active application | Excel Discussion (Misc queries) | |||
MsgBox with condition and showing details | Excel Worksheet Functions | |||
How to read active Excel celll value by external vb application? | Excel Programming | |||
Showing Search results in msgbox? | Excel Programming |