Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Deploying Word userform to use on Existing Document

Posted on 2015-02-15
11
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40612082
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
ID: 40612356
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 27

Expert Comment

by:MacroShadow
ID: 40612453
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40612670
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
ID: 40612959
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40613025
Remove:
    Dim DocFrm As Object
    Set DocFrm = ThisDocument
    DocFrm.ActiveWindow.Visible = True

Open in new window

0
 

Author Comment

by:marrick13
ID: 40613041
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 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 40613112
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
ID: 40613117
Thanks so much for the help!
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40613127
My pleasure.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

730 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