Solved

Deploying Word userform to use on Existing Document

Posted on 2015-02-15
11
207 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:marrick13
  • 5
  • 4
11 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
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
0
 

Author Comment

by:marrick13
Comment Utility
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!
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
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

0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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.
0
 

Author Comment

by:marrick13
Comment Utility
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Remove:
    Dim DocFrm As Object
    Set DocFrm = ThisDocument
    DocFrm.ActiveWindow.Visible = True

Open in new window

0
 

Author Comment

by:marrick13
Comment Utility
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.
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
Comment Utility
When ran from a template "ThisDocument" refers to the template which has no visible windows, hence "ActiveWindow" throwing a runtime 91 error: Object variable or with block variable not set.
0
 

Author Closing Comment

by:marrick13
Comment Utility
Thanks so much for the help!
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
My pleasure.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now