Help on previous posting

[mfgeng_iss]

I originally posted this on Oct 9th and received no replies. Please
HELP!

I have a hydraulic/pneumatic schematic which I’ve animated using
several
macros via the double click method. I want to be able to create a list
of my
macros w/buttons in either Excel or Word that will run my macros when
clicked
on.

Is this even possible?

Thanks
Dave

 

[Paul Herber]

I originally posted this on Oct 9th and received no replies. Please
HELP!

I have a hydraulic/pneumatic schematic which I’ve animated using
several
macros via the double click method. I want to be able to create a list
of my
macros w/buttons in either Excel or Word that will run my macros when
clicked
on.

Is this even possible?

I don't think it is, Dave.
Macros are not visible outside of the document containing them. (As
far as I know).

 

[Scott Helmers]

I have done the opposite -- called Excel macros from Visio (using the code
below). Unfortunately, the Visio application object doesn't have a similar
run method but I thought I'd post this here in case it gives you some ideas.
= = = = = = = = = = = = = =
Sub CallMacroInExcel()
' opens Excel workbook (workbook_open macro, if any, will run)
' then calls specific macros contained in the workbook
'
' Add Reference to Excel to this VBA project before running macro

Dim xlApp As Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlDocPath As String

xlDocPath = "c:\Temp\Excel book with macro.xls"

'Use existing Excel object or create a new one
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") ' get ref if already running
If Err = 429 Then
Set xlApp = CreateObject("Excel.Application")
End If

'Bring Excel to front
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(xlDocPath)

'workbook name must be surrounded by single quotes
xlApp.Run "'" & xlBook.Name & "'!RegularMacro"
'run macro in ThisWorkbook module
xlApp.Run "'" & xlBook.Name & "'!ThisWorkbook.RegularMacro"

End Sub
= = = = = = = = = = = = = =

 

[mfgeng_iss]

Can I create a list, menu, buttons or icons of macros within Visio
that can be viewed and executed from a separate window(monitor)?

 

[Scott Helmers]

Yes. The RUNMACRO function in the shapesheet can call a macro in the same or
a different document. Place one of the following in the DblClick event cell
or any other cell:
1) =RUNMACRO("DoStuff")
2) =RUNMACRO("ThisDocument.DoStuff")
3) =RUNMACRO("DoStuff","MyProjectName")
4) =RUNMACRO("ThisDocument.DoStuff","MyProjectName")

1 and 2 call a macro in the same document (#2 is used when the macro is in
the ThisDocument module).

3 and 4 do the same thing but when the macro is located in a VB project in
another document. NOTE: the other document must be open.

I'm sure there's way to do this in code but don't know what it is off the
top of my head. For a quick a dirty workaround, place the RUNMACRO shapesheet
formula in the EventXFMod cell of a shape then have your code "nudge" the
shape to fire the event by selecting the shape then using
ActiveWindow.Selection.Move 0.0001, 0#

If you want to restore the shape to its orginal position afterwards:
ActiveWindow.Selection.Move -0.0001, 0#

Remember that RUNMACRO will only work if the doc containing the macro is
open so your code may need to open it first.

Anyone have a cleaner solution?

 

[John... Visio MVP]

I don't think it is, Dave.
Macros are not visible outside of the document containing them. (As
far as I know).

The trick is that you have to open an instance of Visio, fully qualify the
references and open the Visio stencil from Excel.

Most of the time I am sending information from Visio to Word or to Excel.

John... Visio MVP

 

[Scott Helmers]

Come to think of it, if you prepare the Visio shapes with the RUNMACRO
function as I suggested in my previous post, there's no reason why you
couldn't "nudge" them Excel or elsewhere. That would allow you to have your
buttons or list in Excel or Word.