topic Re: Msgbox in Excel in VBA Forum

Msgbox in Excel

Anonymous — Wed, 02 Jun 2004 20:06:21 GMT

I would like an AutoCAD VBA program to display a MSGBOX in an opened session of excel, how can you do that?

Joe

Re: Msgbox in Excel

Anonymous — Wed, 02 Jun 2004 20:35:19 GMT

Given my experience with AutoCAD VBA and Excel, I don't think you can, but what you *can* do is have ACAD VBA display a msgbox, then switch focus to Excel. What exactly are you looking to do?? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "JoeMagas" wrote in message news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | I would like an AutoCAD VBA program to display a MSGBOX in an opened session of excel, how can you do that? | | Joe

Re: Msgbox in Excel

Anonymous — Wed, 02 Jun 2004 21:02:35 GMT

I have a block counting program that export values to excel, I need a msgbox as a reminder to fill in the appropriate information.

Do you have some code on changing focus?

Joe

Re: Msgbox in Excel

Anonymous — Wed, 02 Jun 2004 21:02:55 GMT

Matt, you can. Create a wrapper function in xl and use the app's Run method. 'add this function to xl Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() 'first set xlApp an xl Application object xlApp.Run PassMsg("This is a test", vbCritical, "Testing") End Sub -- ---- Ed ---- "Matt W" wrote in message news:40be39fb_1@newsprd01... Given my experience with AutoCAD VBA and Excel, I don't think you can, but what you *can* do is have ACAD VBA display a msgbox, then switch focus to Excel. What exactly are you looking to do?? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "JoeMagas" wrote in message news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | I would like an AutoCAD VBA program to display a MSGBOX in an opened session of excel, how can you do that? | | Joe

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 12:23:44 GMT

Hi Ed I tried your code but... it displayed the msgbox inside of AutoCAD and not in Excel. Here's what I used (after adding a reference to Excel). Option Explicit ' Application - Excel Public oExcel As Excel.Application Public oBook As Excel.Workbook Public oSheet As Excel.Worksheet Public Sub CreateExcel() Dim i As Integer Set oExcel = CreateObject("Excel.Application") Set oBook = oExcel.Workbooks.Add Set oSheet = oBook.Worksheets(1) oExcel.Visible = True End Sub Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() CreateExcel 'first set xlApp an xl Application object oExcel.Run PassMsg("This is a test", vbCritical, "Testing") End Sub I think (if I understand Joe correctly, and maybe he can step in and verify) what he wants is the msgbox to be shown in Excel. If that's the case, wouldn't the code for the msgbox need to reside somewhere within the workbook or a worksheet, then you can call that sub from AutoCAD, right? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "Ed Jobe" wrote in message news:40be4081$1_2@newsprd01... Matt, you can. Create a wrapper function in xl and use the app's Run method. 'add this function to xl Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() 'first set xlApp an xl Application object xlApp.Run PassMsg("This is a test", vbCritical, "Testing") End Sub -- ---- Ed ---- "Matt W" wrote in message news:40be39fb_1@newsprd01... Given my experience with AutoCAD VBA and Excel, I don't think you can, but what you *can* do is have ACAD VBA display a msgbox, then switch focus to Excel. What exactly are you looking to do?? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "JoeMagas" wrote in message news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | I would like an AutoCAD VBA program to display a MSGBOX in an opened session of excel, how can you do that? | | Joe

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 14:22:48 GMT

That's what Ed shows. 'add this function to __xl__ [emphasis mine] Function PassMsg(sPrompt As String, _ ;^) -- R. Robert Bell "Matt W" wrote in message news:40bf1845_2@newsprd01... Hi Ed I tried your code but... it displayed the msgbox inside of AutoCAD and not in Excel. Here's what I used (after adding a reference to Excel). Option Explicit ' Application - Excel Public oExcel As Excel.Application Public oBook As Excel.Workbook Public oSheet As Excel.Worksheet Public Sub CreateExcel() Dim i As Integer Set oExcel = CreateObject("Excel.Application") Set oBook = oExcel.Workbooks.Add Set oSheet = oBook.Worksheets(1) oExcel.Visible = True End Sub Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() CreateExcel 'first set xlApp an xl Application object oExcel.Run PassMsg("This is a test", vbCritical, "Testing") End Sub I think (if I understand Joe correctly, and maybe he can step in and verify) what he wants is the msgbox to be shown in Excel. If that's the case, wouldn't the code for the msgbox need to reside somewhere within the workbook or a worksheet, then you can call that sub from AutoCAD, right? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "Ed Jobe" wrote in message news:40be4081$1_2@newsprd01... Matt, you can. Create a wrapper function in xl and use the app's Run method. 'add this function to xl Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() 'first set xlApp an xl Application object xlApp.Run PassMsg("This is a test", vbCritical, "Testing") End Sub -- ---- Ed ---- "Matt W" wrote in message news:40be39fb_1@newsprd01... Given my experience with AutoCAD VBA and Excel, I don't think you can, but what you *can* do is have ACAD VBA display a msgbox, then switch focus to Excel. What exactly are you looking to do?? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "JoeMagas" wrote in message news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | I would like an AutoCAD VBA program to display a MSGBOX in an opened session of excel, how can you do that? | | Joe

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 14:26:03 GMT

"wouldn't the code for the msgbox need to reside somewhere within the workbook " Notice the comment, "'add this function to xl" above the function. You could add it to a specific xls, use vbe to add it to the xls, or create an xla so that it is always available. -- ---- Ed ---- "Matt W" wrote in message news:40bf1845_2@newsprd01... Hi Ed I tried your code but... it displayed the msgbox inside of AutoCAD and not in Excel. Here's what I used (after adding a reference to Excel). Option Explicit ' Application - Excel Public oExcel As Excel.Application Public oBook As Excel.Workbook Public oSheet As Excel.Worksheet Public Sub CreateExcel() Dim i As Integer Set oExcel = CreateObject("Excel.Application") Set oBook = oExcel.Workbooks.Add Set oSheet = oBook.Worksheets(1) oExcel.Visible = True End Sub Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() CreateExcel 'first set xlApp an xl Application object oExcel.Run PassMsg("This is a test", vbCritical, "Testing") End Sub I think (if I understand Joe correctly, and maybe he can step in and verify) what he wants is the msgbox to be shown in Excel. If that's the case, wouldn't the code for the msgbox need to reside somewhere within the workbook or a worksheet, then you can call that sub from AutoCAD, right? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "Ed Jobe" wrote in message news:40be4081$1_2@newsprd01... Matt, you can. Create a wrapper function in xl and use the app's Run method. 'add this function to xl Function PassMsg(sPrompt As String, _ Optional cButtons As VbMsgBoxStyle, _ Optional sTitle As String) MsgBox sPrompt, cButtons, sTitle End Function ' 'run this from acad Sub test() 'first set xlApp an xl Application object xlApp.Run PassMsg("This is a test", vbCritical, "Testing") End Sub -- ---- Ed ---- "Matt W" wrote in message news:40be39fb_1@newsprd01... Given my experience with AutoCAD VBA and Excel, I don't think you can, but what you *can* do is have ACAD VBA display a msgbox, then switch focus to Excel. What exactly are you looking to do?? -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "JoeMagas" wrote in message news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | I would like an AutoCAD VBA program to display a MSGBOX in an opened session of excel, how can you do that? | | Joe

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 15:23:09 GMT

Yes I would like Excel to display the dialog box (or at least appear that way)

Once the user has exported the data he needs to be reminded to complete some sections of the excel document before sending out.

Thanks all for your help,
Joe

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 18:09:28 GMT

Oh yeah... duh. Well what do you expect... it wasn't even 8:30. Sorry. As for you Joe, here's what I would do: 1) Take the code that Ed wrote (modify it if need be) and put it into an Excel workbook (this would be easier, in my opinion, than having AutoCAD write the code to a module everytime). 2) Save that workbook as a template (*.XLT). 3) Have your ACAD VBA code open the template and write the block data to it. 4) Either save the workbook automatically or prompt the user for a location to save it. My 2 cents. -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "R. Robert Bell" wrote in message news:40bf3432_3@newsprd01... | That's what Ed shows. | | 'add this function to __xl__ [emphasis mine] | Function PassMsg(sPrompt As String, _ | | | ;^) | | -- | R. Robert Bell | | | "Matt W" wrote in message | news:40bf1845_2@newsprd01... | Hi Ed | I tried your code but... it displayed the msgbox inside of AutoCAD and not | in Excel. Here's what I used (after adding a reference to Excel). | | Option Explicit | | ' Application - Excel | Public oExcel As Excel.Application | Public oBook As Excel.Workbook | Public oSheet As Excel.Worksheet | | Public Sub CreateExcel() | Dim i As Integer | Set oExcel = CreateObject("Excel.Application") | Set oBook = oExcel.Workbooks.Add | Set oSheet = oBook.Worksheets(1) | | oExcel.Visible = True | End Sub | | Function PassMsg(sPrompt As String, _ | Optional cButtons As VbMsgBoxStyle, _ | Optional sTitle As String) | MsgBox sPrompt, cButtons, sTitle | End Function | ' | 'run this from acad | Sub test() | CreateExcel | 'first set xlApp an xl Application object | oExcel.Run PassMsg("This is a test", vbCritical, "Testing") | End Sub | | I think (if I understand Joe correctly, and maybe he can step in and verify) | what he wants is the msgbox to be shown in Excel. | If that's the case, wouldn't the code for the msgbox need to reside | somewhere within the workbook or a worksheet, then you can call that sub | from AutoCAD, right? | | -- | Matt W | | There are 3 kinds of people: | Those who can count, and those who can't. | | "Ed Jobe" wrote in message | news:40be4081$1_2@newsprd01... | Matt, you can. Create a wrapper function in xl and use the app's Run | method. | | 'add this function to xl | Function PassMsg(sPrompt As String, _ | Optional cButtons As VbMsgBoxStyle, _ | Optional sTitle As String) | MsgBox sPrompt, cButtons, sTitle | End Function | ' | 'run this from acad | Sub test() | 'first set xlApp an xl Application object | xlApp.Run PassMsg("This is a test", vbCritical, "Testing") | End Sub | | -- | ---- | Ed | ---- | "Matt W" wrote in message | news:40be39fb_1@newsprd01... | Given my experience with AutoCAD VBA and Excel, I don't think you can, | but | what you *can* do is have ACAD VBA display a msgbox, then switch focus | to | Excel. | | What exactly are you looking to do?? | | | -- | Matt W | | There are 3 kinds of people: | Those who can count, and those who can't. | | | "JoeMagas" wrote in message | news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | | I would like an AutoCAD VBA program to display a MSGBOX in an opened | session of excel, how can you do that? | | | | Joe | | |

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 18:41:47 GMT

I would save it in an xla, addin file. Start a new xls. Add the macro to ThisWorkbook. Save the file as an addin using the "Files of type" combo. Save it to one of the XLSTART folders and it will auto load when xl starts and you won't need to add the code to every file. -- ---- Ed ---- "Matt W" wrote in message news:40bf694e$1_2@newsprd01... Oh yeah... duh. Well what do you expect... it wasn't even 8:30. Sorry. As for you Joe, here's what I would do: 1) Take the code that Ed wrote (modify it if need be) and put it into an Excel workbook (this would be easier, in my opinion, than having AutoCAD write the code to a module everytime). 2) Save that workbook as a template (*.XLT). 3) Have your ACAD VBA code open the template and write the block data to it. 4) Either save the workbook automatically or prompt the user for a location to save it. My 2 cents. -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "R. Robert Bell" wrote in message news:40bf3432_3@newsprd01... | That's what Ed shows. | | 'add this function to __xl__ [emphasis mine] | Function PassMsg(sPrompt As String, _ | | | ;^) | | -- | R. Robert Bell | | | "Matt W" wrote in message | news:40bf1845_2@newsprd01... | Hi Ed | I tried your code but... it displayed the msgbox inside of AutoCAD and not | in Excel. Here's what I used (after adding a reference to Excel). | | Option Explicit | | ' Application - Excel | Public oExcel As Excel.Application | Public oBook As Excel.Workbook | Public oSheet As Excel.Worksheet | | Public Sub CreateExcel() | Dim i As Integer | Set oExcel = CreateObject("Excel.Application") | Set oBook = oExcel.Workbooks.Add | Set oSheet = oBook.Worksheets(1) | | oExcel.Visible = True | End Sub | | Function PassMsg(sPrompt As String, _ | Optional cButtons As VbMsgBoxStyle, _ | Optional sTitle As String) | MsgBox sPrompt, cButtons, sTitle | End Function | ' | 'run this from acad | Sub test() | CreateExcel | 'first set xlApp an xl Application object | oExcel.Run PassMsg("This is a test", vbCritical, "Testing") | End Sub | | I think (if I understand Joe correctly, and maybe he can step in and verify) | what he wants is the msgbox to be shown in Excel. | If that's the case, wouldn't the code for the msgbox need to reside | somewhere within the workbook or a worksheet, then you can call that sub | from AutoCAD, right? | | -- | Matt W | | There are 3 kinds of people: | Those who can count, and those who can't. | | "Ed Jobe" wrote in message | news:40be4081$1_2@newsprd01... | Matt, you can. Create a wrapper function in xl and use the app's Run | method. | | 'add this function to xl | Function PassMsg(sPrompt As String, _ | Optional cButtons As VbMsgBoxStyle, _ | Optional sTitle As String) | MsgBox sPrompt, cButtons, sTitle | End Function | ' | 'run this from acad | Sub test() | 'first set xlApp an xl Application object | xlApp.Run PassMsg("This is a test", vbCritical, "Testing") | End Sub | | -- | ---- | Ed | ---- | "Matt W" wrote in message | news:40be39fb_1@newsprd01... | Given my experience with AutoCAD VBA and Excel, I don't think you can, | but | what you *can* do is have ACAD VBA display a msgbox, then switch focus | to | Excel. | | What exactly are you looking to do?? | | | -- | Matt W | | There are 3 kinds of people: | Those who can count, and those who can't. | | | "JoeMagas" wrote in message | news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | | I would like an AutoCAD VBA program to display a MSGBOX in an opened | session of excel, how can you do that? | | | | Joe | | |

Re: Msgbox in Excel

Anonymous — Thu, 03 Jun 2004 19:10:03 GMT

An Add-In. Hmmmmm.... I hadn't thougth of that. I guess there *is* more than one way to skin the proverbial cat. -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "Ed Jobe" wrote in message news:40bf70ec$1_1@newsprd01... I would save it in an xla, addin file. Start a new xls. Add the macro to ThisWorkbook. Save the file as an addin using the "Files of type" combo. Save it to one of the XLSTART folders and it will auto load when xl starts and you won't need to add the code to every file. -- ---- Ed ---- "Matt W" wrote in message news:40bf694e$1_2@newsprd01... Oh yeah... duh. Well what do you expect... it wasn't even 8:30. Sorry. As for you Joe, here's what I would do: 1) Take the code that Ed wrote (modify it if need be) and put it into an Excel workbook (this would be easier, in my opinion, than having AutoCAD write the code to a module everytime). 2) Save that workbook as a template (*.XLT). 3) Have your ACAD VBA code open the template and write the block data to it. 4) Either save the workbook automatically or prompt the user for a location to save it. My 2 cents. -- Matt W There are 3 kinds of people: Those who can count, and those who can't. "R. Robert Bell" wrote in message news:40bf3432_3@newsprd01... | That's what Ed shows. | | 'add this function to __xl__ [emphasis mine] | Function PassMsg(sPrompt As String, _ | | | ;^) | | -- | R. Robert Bell | | | "Matt W" wrote in message | news:40bf1845_2@newsprd01... | Hi Ed | I tried your code but... it displayed the msgbox inside of AutoCAD and not | in Excel. Here's what I used (after adding a reference to Excel). | | Option Explicit | | ' Application - Excel | Public oExcel As Excel.Application | Public oBook As Excel.Workbook | Public oSheet As Excel.Worksheet | | Public Sub CreateExcel() | Dim i As Integer | Set oExcel = CreateObject("Excel.Application") | Set oBook = oExcel.Workbooks.Add | Set oSheet = oBook.Worksheets(1) | | oExcel.Visible = True | End Sub | | Function PassMsg(sPrompt As String, _ | Optional cButtons As VbMsgBoxStyle, _ | Optional sTitle As String) | MsgBox sPrompt, cButtons, sTitle | End Function | ' | 'run this from acad | Sub test() | CreateExcel | 'first set xlApp an xl Application object | oExcel.Run PassMsg("This is a test", vbCritical, "Testing") | End Sub | | I think (if I understand Joe correctly, and maybe he can step in and verify) | what he wants is the msgbox to be shown in Excel. | If that's the case, wouldn't the code for the msgbox need to reside | somewhere within the workbook or a worksheet, then you can call that sub | from AutoCAD, right? | | -- | Matt W | | There are 3 kinds of people: | Those who can count, and those who can't. | | "Ed Jobe" wrote in message | news:40be4081$1_2@newsprd01... | Matt, you can. Create a wrapper function in xl and use the app's Run | method. | | 'add this function to xl | Function PassMsg(sPrompt As String, _ | Optional cButtons As VbMsgBoxStyle, _ | Optional sTitle As String) | MsgBox sPrompt, cButtons, sTitle | End Function | ' | 'run this from acad | Sub test() | 'first set xlApp an xl Application object | xlApp.Run PassMsg("This is a test", vbCritical, "Testing") | End Sub | | -- | ---- | Ed | ---- | "Matt W" wrote in message | news:40be39fb_1@newsprd01... | Given my experience with AutoCAD VBA and Excel, I don't think you can, | but | what you *can* do is have ACAD VBA display a msgbox, then switch focus | to | Excel. | | What exactly are you looking to do?? | | | -- | Matt W | | There are 3 kinds of people: | Those who can count, and those who can't. | | | "JoeMagas" wrote in message | news:23683704.1086206811347.JavaMail.jive@jiveforum2.autodesk.com... | | I would like an AutoCAD VBA program to display a MSGBOX in an opened | session of excel, how can you do that? | | | | Joe | | |

Re: Msgbox in Excel

Anonymous — Mon, 07 Jun 2004 17:21:42 GMT

Thanks everyone for your help with this:

I am very close, but still having a bit of trouble.

Here is my code:

Set objExcel = GetObject(, "Excel.application")
If Err <> 0 Then
Err.Clear
Set objExcel = CreateObject("Excel.application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
objExcel.Visible = True

Set wkbkobj = objExcel.Workbooks.Open(strFileName) 'opens a "template" file

objExcel.Run PassMsg("This is a test", vbCritical, "Testing")

When I run the above I get this error message (in AutoCAD):

Complile error:
Sub or Function not defined.

I suspect that I may be saving the function in Excel in the wrong place.

Any ideas or suggestions:
Joe

Re: Msgbox in Excel

Anonymous — Mon, 07 Jun 2004 17:35:31 GMT

You didn't say where you saved it. -- ---- Ed ---- "JoeMagas" wrote in message news:2439981.1086628933162.JavaMail.jive@jiveforum2... Thanks everyone for your help with this: I am very close, but still having a bit of trouble. Here is my code: Set objExcel = GetObject(, "Excel.application") If Err <> 0 Then Err.Clear Set objExcel = CreateObject("Excel.application") If Err <> 0 Then MsgBox "Could not start Excel", vbExclamation End End If End If objExcel.Visible = True Set wkbkobj = objExcel.Workbooks.Open(strFileName) 'opens a "template" file objExcel.Run PassMsg("This is a test", vbCritical, "Testing") When I run the above I get this error message (in AutoCAD): Complile error: Sub or Function not defined. I suspect that I may be saving the function in Excel in the wrong place. Any ideas or suggestions: Joe

Re: Msgbox in Excel

Anonymous — Mon, 07 Jun 2004 17:56:40 GMT

I saved it (in Excel VBA) under Microsoft Excel Objects in (General) in the Excel file that I load.

Joe

Re: Msgbox in Excel

Anonymous — Mon, 07 Jun 2004 19:47:42 GMT

Joe, if you saved it in that xls, then it is only available in that xls. When you open a new xls from a template as your code shows, the macro is not there. You will either have to save it in the template or save it in an add-in. I reccommend using an add-in, because if you put it in a template, it will unnecissarily add it to every xls. See the other post I made to Matt. -- ---- Ed ---- "JoeMagas" wrote in message news:28358160.1086631030435.JavaMail.jive@jiveforum2.autodesk.com... I saved it (in Excel VBA) under Microsoft Excel Objects in (General) in the Excel file that I load. Joe