asked on

Deploying Word userform to use on Existing Document

This must be a basic VBA question but I haven't been able to figure it out. While I can set up a Word userform stored in a template and run it when creating a new document based on that template, I don't know how to run a userform from an existing document when that userform is stored in a template.

I have attached a userform and code (saved as a .doc file) that splits space-separated text in a selected table column into separate words in columns to its right. (It includes a simple table for demo purposes).

I have code in the Initialize module to hide the document in which the userform and code are stored (DocFrm.ActiveWindow.Visible) set to "True" until I can figure out how to deploy this (otherwise I won't be able to see the document).

I was hoping to be able to run this code from the regular Macro menu as I would macros stored in my global template in the Startup folder. I understand that the proper way to store both code and a userform is in a macro-enabled template, but my attempts to run it successfully from such a template haven't worked.

Can someone just give me a few simple steps I can follow to run this 'Split Names' program from any Word document?
Split-Names-FEB-15-TEST.doc

Joe Howard

Save your file in the Startup folder.

To find it:
1. Open Word Options.
2. Click Advanced.
3. Under General, click File Locations.
4. Save the template that you want available when you start Word to the location that is listed for Startup.
For Windows 7 it's:
C:\Users\%UserName%\AppData\Roaming\Microsoft\Word\STARTUP

marrick13

ASKER

Thanks, but the Startup folder was the first thing I thought of (I already have a global template file there that contains a host of macros I use regularly), but when I stored a copy of the macro-enabled template containing the Split Names Same Doc userform and code in Startup and tried to run it from another document. I was getting a compile error ("Compile error in hidden module: Split_Names_Same_Doc. This error commonly occurs...").

I then changed the module that shows the form to a public one so it would show in the macros menu:

Public Sub Split_Names_Same_Doc()
frmSplit.Show
End Sub

...but now I get a run time 91 error when I try to run it from the macros menu. So maybe the problem is in knowing how to run the program I still need expert help!

Joe Howard

1. Remove all duplicate code (the WordCount function exists 3 times).
2. Make sure there are no modules and subs\functions with the same name.
3. Save your file as a template in the Startup folder
4. Add this code to a standard module in your file, it will be available from any open Word document, running it will display your form.

Public Sub TestMe()
    frmSplit.Show
End Sub

Open in new window

Helen Feddema

So long as the UserForm is stored in the template, it should be available in any document made from that template. I have used this technique for many Office versions now. The template doesn't need to be in the Startup folder, in my experience.

marrick13

ASKER

This is still not working for me; I still get error 91. I removed the extra WordCount modules, so there is no duplicate code. There are no subs or functions with the same name (and never were that I can see). Here are the names:

Module Name Sub/Function Name
Get_Word_Count Function WordCount
ParseCol Sub Parse_Selected_Column
Select_Rng Sub Select_Range
Split_Names_Same Public Sub Split_Names_Same_Doc
Test_Selctn Sub Test_Selection

I had deliberately avoided making the module names match their sub names because that's caused problems in the past when running macros from a global template.

I've reattached the file with duplicates removed. I'm using Windows 7, the macro appears in the macros menu because it's stored in the Startup folder. But I keep getting runtime 91. Any other suggestions?
Split-Names-FEB-15-TEST.doc

Joe Howard

Remove:

    Dim DocFrm As Object
    Set DocFrm = ThisDocument
    DocFrm.ActiveWindow.Visible = True

Open in new window

marrick13

ASKER

Ah-that did the trick. Before your reply, I started creating a new file with a simple userform with the "show" statement. That worked fine, so I continued adding more to the userform, and was going to add code piece by piece. You saved me all that time. But the question is why that code got in the way. I was concerned from previous userform experience about the document holding the userform would "get in the way" and thought I should make it invisible after declaring it as an object. I can see that this isn't necessary but I don't know why those three lines caused the runtime error. If you can explain I'd appreciate it and will then mark it as the solution. Thanks.

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrick13

ASKER

Thanks so much for the help!

Joe Howard

My pleasure.