Msgbox in Excel

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

Joe

 

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.


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

 

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

 

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
----
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.


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

 

That's what Ed shows.
<clip>
'add this function to __xl__ [emphasis mine]
Function PassMsg(sPrompt As String, _
</clip>

;^)

--
R. Robert Bell


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.

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
----
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.


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

 

"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
----
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.

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
----
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.


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

 

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

 

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.

| That's what Ed shows.
| <clip>
| 'add this function to __xl__ [emphasis mine]
| Function PassMsg(sPrompt As String, _
| </clip>
|
| ;^)
|
| --
| R. Robert Bell
|
|
| | 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.
|
| | 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
| ----
| | 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.
|
|
| | | I would like an AutoCAD VBA program to display a MSGBOX in an opened
| session of excel, how can you do that?
| |
| | Joe
|
|
|

 

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
----
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.

| That's what Ed shows.
| <clip>
| 'add this function to __xl__ [emphasis mine]
| Function PassMsg(sPrompt As String, _
| </clip>
|
| ;^)
|
| --
| R. Robert Bell
|
|
| | 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.
|
| | 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
| ----
| | 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.
|
|
| | | I would like an AutoCAD VBA program to display a MSGBOX in an opened
| session of excel, how can you do that?
| |
| | Joe
|
|
|

 

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

 

You didn't say where you saved it.

--
----
Ed
----
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

 

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

Joe

 

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.