1

Given that I have a UserForm embedded in one of the following:

  • an Excel workbook named c:\myBook.xslm
  • a Word document c:\myDocument.docm, or
  • a PowerPoint presentation named c:\myPresentation.ppm

What Automation properties / methods do I need to use in order to open and display the UserForm from an external script / host application / program?

For example, let's say I have the following JScript running under WSH:

var app = new ActiveXObject('Excel.Application');
app.Visible = true;
var book = app.Workbooks.Open('c:\myBook.xlsm');

// open UserForm here

How would I proceed to open the UserForm?

Note: I am looking for a solution that would work with an arbitrary document. This precludes manually (but not programmatically as part of the script) adding a Sub to show the UserForm, which can be called from the external script.

Improve this question
2
  • Can you add the target Office Version you try to automate? Commented May 8, 2018 at 13:13
  • @BrunoBieri 2010+; does it matter? Commented May 8, 2018 at 13:14

1 Answer 1

Reset to default
0

An idea is to override the document open mehtods. At least for Microsoft Word and Excel:

' Word
Private Sub Document_Open()
  UserForm1.Show
End Sub

' Excel
Private Sub Workbook_Open()
  UserForm1.Show
End Sub

At word and excel document open, the dialog will be shown.

For Powerpoint its a bit more complicated:

How to auto execute a macro when opening a Powerpoint presentation?

Update

After additional information in the question this is not a solution anymore. A starting point how to create code an add it to a VBA project can be found here: https://stackoverflow.com/a/34838194/1306012

Also this website provides additional information: http://www.cpearson.com/excel/vbe.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

In the c:\myBook.xlsm add a method Workbook_Open(). This method will be executed at opening the Workbook. Once your code executes the line var book = app.Workbooks.Open('c:\myBook.xlsm'); the mothod should be called and therefore your userform being showed.
Ah, I understand. After your question update my proposal is not applicable anymore.
The only thing I can think of is to use the Run method but also for that you need a macro in the document to call which would show the UserForm. msdn.microsoft.com/en-us/vba/excel-vba/articles/… To me there is no way to directly call a UserForm from "outside" a VBA macro itself.
Unless it's possible to create such a macro from the external program, and then call the created macro.
I would like to proof me wrong but to my knowledge the macros are saved in a binary format within the document. In case you know how to create this binary format yourself, modify the document, save it temporarly and then open it... Then I could work. By the way, how do you know how the name of the UserForm within a document if you don't know the document itself?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.