How to call macros and userforms from another template

[Anne P.]

Hi,

I am using Windows XP Professional and both Word XP and 2003. I am having a
problem calling macros and/or userforms from a global template. I and many
other people have asked this question before in varying ways, and I have not
seen a workable answer yet. Here's the situation:

I have a template named SKGLobal which is in the startup directory. I have
other templates in the templates directory (Letter, Memo, Fax, etc.) which
need to call macros and userforms from SKGlobal. Since the startup
directory is different on each user's machine (C:\Documents and
Settings\username\Application Data\Microsoft\Word\Startup), I can't see how
I could set a reference to SKGlobal.

I have tried using Application.Run templatename.modulename.macroname and it
works for most macros. However, I have two userforms in SKGlobal that I
need to call. I have tried just frmReLine.show and it doesn't work. I have
tried skglobal.frmReLine.show and it doesn't work. I have tried
skglobal.forms.frmReLine.show and it doesn't work. I even created a public
sub in SKGlobal named CreateReLine and placed the line frmReLine.show in
that sub. I then used Application.Run skglobal.skmacros.CreateReLine and it
doesn't work.

Then I decided to place SKGlobal in the place it used to be before Windows
2000 (C:\Program Files\Microsoft Office\Office9\Startup) and changed the
Tools, Options, File Location to that directory. I went to the VB Editor
and set a reference to SKGlobal. It still doesn't work. I cannot get my
userform to load from within another userform. I keep getting an error:

Run-time error '424':
Object required

Just in case it is something else in the code for my Memo template userform,
frmMemo, here is all of the code from that template:

Dim objAtE As AutoTextEntry
Dim objPersonal As Template
Dim objGlobal As Template
Dim ctrl As Control
Dim bmRange As Range
Dim strAddress As String
Dim strTo As String
Dim strCC As String
Dim lngPos As String
Dim strFind As String
Dim strReplace As String
Dim strGlobalPath As String
Dim strPersonalPath As String

Private Sub chkLetterhead_Click()
If chkLetterhead.Value = False Then
cmbOffices.Enabled = False
Else
cmbOffices.Enabled = True
cmbOffices.SetFocus
End If
End Sub

Private Sub cmbReLine_Change()
Dim strReLine As String
'fill the txtReLine text box with AutoText Value
For Each objAtE In objPersonal.AutoTextEntries
If objAtE.Name = cmbReLine.Value Then
strReLine = objAtE.Value
End If
Next objAtE
txtReLine.Text = strReLine
End Sub

Private Sub cmdAtty_Click()
frmUserInfo.Show
End Sub

Private Sub cmdCancel_Click()
If MsgBox("Are you sure?", vbYesNo + vbInformation, _
"Seward & Kissel Memo") = vbYes Then
Unload Me
ActiveDocument.Close (wdDoNotSaveChanges)
End If
End Sub

Private Sub cmdOk_Click()
strGlobalPath = Options.DefaultFilePath(wdStartupPath) & "\SKGlobal.dot"
Set objGlobal = Templates(strGlobalPath)
strPersonalPath = Options.DefaultFilePath(wdStartupPath) & "\Personal.dot"
Set objPersonal = Templates(strPersonalPath)

Unload Me
'Insert today's date
If ActiveDocument.Bookmarks.Exists("Date") Then
Set bmRange = ActiveDocument.Bookmarks("Date").Range
bmRange.Text = Format(Now, "mmmm d, YYYY")
ActiveDocument.Bookmarks.Add Name:="Date", Range:=bmRange
End If

'Insert the logo
If chkLetterhead.Value = True And cmbOffices.Value <> "" Then
If ActiveDocument.Bookmarks.Exists("Logo") Then
Set bmRange = ActiveDocument.Bookmarks("Logo").Range
bmRange.Text = cmbOffices.Value
ActiveDocument.Bookmarks.Add Name:="Logo", Range:=bmRange
bmRange.InsertAutoText
ActiveDocument.Bookmarks.Add Name:="Logo", Range:=bmRange
End If
End If
' Delete CC & Re row from table if CC & Re text box is empty
If txtCC.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="txtCC"
Selection.Rows.Delete
End If
If txtReLine.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="txtReLine"
Selection.Rows.Delete
End If

'Insert re line
If ActiveDocument.Bookmarks.Exists("ReLine") Then
Set bmRange = ActiveDocument.Bookmarks("ReLine").Range
bmRange.Text = txtReLine.Value
ActiveDocument.Bookmarks.Add Name:="ReLine", Range:=bmRange
End If


'Loop thru controls and find ones that match bookmark name and then fill
bookmark
'with contents of control

For Each ctrl In Me.Controls
If ActiveDocument.Bookmarks.Exists(ctrl.Name) Then
Set bmRange = ActiveDocument.Bookmarks(ctrl.Name).Range
bmRange = ctrl.Text
ActiveDocument.Bookmarks.Add Name:=ctrl.Name, Range:=bmRange
End If
Next ctrl

'go to start bookmark and apply body text style

Selection.GoTo What:=wdGoToBookmark, Name:="Start"
If optSingle = True Then
Selection.Style = ActiveDocument.Styles("Body Text")
Else
Selection.Style = ActiveDocument.Styles("Body Text 2")
End If
ActiveDocument.Bookmarks("Start").Delete
End Sub
Private Sub cmdReLine_Click()
frmReLine.Show
End Sub
Private Sub cmdTo_Click()


strFind = ", "
strReplace = vbCrLf

strAddress = Application.GetAddress( _
, , True, 1, 2, , True, True)

If strAddress = "" Then
MsgBox "No addressee(s) selected."
Exit Sub
End If
'everything after Chr(13)is a CC returned from GetAddress

lngPos = InStr(1, strAddress, Chr(13))

If lngPos < Len(strAddress) Then
strTo = Left(Left(strAddress, lngPos), Len(Left(strAddress, lngPos)) - 1)
strTo = Replace(strTo, strFind, strReplace)

strCC = Left(Right(strAddress, Len(strAddress) - lngPos),
Len(Right(strAddress, Len(strAddress) - lngPos)) - 1)
strCC = Replace(strCC, strFind, strReplace)
Else
strTo = Left(strAddress, Len(strAddress) - 1)
End If

'Fill the To and CC text boxes
If txtTo = "" Then
txtTo.Text = strTo
Else
If strTo > "" Then
txtTo.Text = txtTo.Text & ", " & strTo
End If
End If
If txtCC = "" Then
txtCC.Text = strCC
Else
If strCC > "" Then
txtCC.Text = txtCC.Text & ", " & strCC
End If
End If
End Sub

Private Sub UserForm_Initialize()
strGlobalPath = Options.DefaultFilePath(wdStartupPath) & "\SKGlobal.dot"
Set objGlobal = Templates(strGlobalPath)
strPersonalPath = Options.DefaultFilePath(wdStartupPath) & "\Personal.dot"
Set objPersonal = Templates(strPersonalPath)

'Fill Offices combobox with letterhead choices from SKGlobal
For Each objAtE In objGlobal.AutoTextEntries
If objAtE.StyleName = "logoAddress" Then
If Left(objAtE.Name, 2) = "NY" Or Left(objAtE.Name, 2) = "DC" Then
cmbOffices.AddItem objAtE.Name
End If
End If
Next objAtE
'Fill Re Line combobox with relines
For Each objAtE In objPersonal.AutoTextEntries
If objAtE.StyleName = "ReLine" Then
cmbReLine.AddItem objAtE.Name
End If
Next objAtE
'Fill user combobox
For Each objAtE In objPersonal.AutoTextEntries
If objAtE.StyleName = "UserInfo" Then
cmbAtty.AddItem objAtE.Name
End If
Next objAtE
End Sub

So far, the only code above that is not working correctly is the portion to
insert a letterhead image from Autotext (I am still working on that part),
and the cmdAtty_click event and cmdReLine_click event. I cannot get the
userforms to open.

I am at a loss here and I really need to be able to use these buttons. Is
there something I am missing here? Maybe it is a setting on my frmMemo
userform that is preventing these other userforms from loading. I would
really appreciate any input on what I might do here.

Thanks,
Anne P.

 

[Charles Kenyon]

Write a macro in your global template that calls your userform (also in your
global).
i.e. Project: MyGlobal
Module: MyModule
UserForm: frmAddress
Macro: MyUserFormShow

sub MyUserFormShow()
Dim myForm As frmAddress
Set myForm = New frmAddress
myForm.Show
Unload myForm
Set myForm = Nothing
end Sub

To run this from another template, regardless of the location of your global
template so long as it is loaded,
Application.Run MacroName:= "MyUserFormShow"
or
Application.Run MacroName:= "MyGlobal.MyModule.MyUserFormShow"

You would use the same method to call other macros in your global.

You can also build a custom toolbar in your global and attach your macros to
buttons on that toolbar. Then you don't need code in your other templates at
all. You could put a document variable in templates that are designed to
work with your UserForm and test for that variable when running your code so
that you wouldn't be using the UserForm in an inappropriate situation.
--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://www.mvps.org/word which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[Anne P.]

Charles,

Thanks, I will give that a try and let you know.

Anne p.

 

[Anne P.]

Hi,
I just placed the following in my global template (SKGlobal.dot):

Public Sub CreateReLine()
Dim myForm As frmReLine
Set myForm = New frmReLine
myForm.Show
Unload myForm
Set myForm = Nothing
End Sub

And I placed the following the cmdReLine_Click event in my memo template:

Private Sub cmdReLine_Click()
Application.Run MacroName:=SKGlobal.SKMacros.CreateReLine
End Sub

I am still getting the same error message and the userform will not load.

I need to be able to run these two userforms from the letter, memo and fax
templates. If a user needs to add another attorney to her list of attorneys
she creates work for, or needs to create a new custom reline for insertion
into a letter or memo, she needs to be able to do that from the userform for
the letter, memo or fax template. If the buttons don't work from the letter
form, she would have to exit out of the letter, call another macro to create
the new user or reline, then restart the letter.

Thanks,
Anne P.

 

[Jonathan West]

Hi,
I just placed the following in my global template (SKGlobal.dot):

Public Sub CreateReLine()
Dim myForm As frmReLine
Set myForm = New frmReLine
myForm.Show
Unload myForm
Set myForm = Nothing
End Sub

And I placed the following the cmdReLine_Click event in my memo template:

Private Sub cmdReLine_Click()
Application.Run MacroName:=SKGlobal.SKMacros.CreateReLine
End Sub

You need to put quotes round the macro name

Application.Run MacroName:="SKGlobal.SKMacros.CreateReLine"


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Anne P.]

Thank you. I don't know how I missed the quotes in Charles' message, but I
did. It works now.

Anne P.