Userform

[discostu1975]

I am working on a userform that will distribute the inputted information to
bookmarks in a word document. This part works flawlessly. Once the userform
has been filled out I am presented with Document1 that has all of the updated
information in about 5 sections. The UserForm is called from a macro called
AutoNew in which the only code is Show UserForm1

I have a macro (BreakOnSection) that I can run that will then take each of
the sections and save them as separate files to any specified location. This
too works flawlessly when called from within a document.

What I would like to have happen is once the user clicks my control button
in UserForm1, it will do both of the above processes so that it eliminates
the need for them to run the macro separately.

I have tried to copy the text of the BreakOnSection Macro directly into the
command button and I've tried to do a call to it (as illustrated below).
I've even tried to incorporate it as part of AutoNew. The problem is that
I'm not getting any error messages right now but the macro is not running.

I snipped out a bunch of the bookmark moves, but have included the remainder
of the code.



Private Sub CommandButton1_Click()
With ActiveDocument
..Bookmarks("purchaser").Range _
..InsertBefore purchaser
..Bookmarks("purchaser2").Range _
~snip~
End With
Call BreakOnSection
UserForm1.Hide


End Sub


Here is the macro text too



Sub BreakOnSection()
' Used to set criteria for moving through the document by section.
Application.Browser.Target = wdBrowseSection

'A mailmerge document ends with a section break next page.
'Subtracting one from the section count stop error message.
For i = 1 To ((ActiveDocument.Sections.Count) - 1)

'Select and copy the section text to the clipboard
ActiveDocument.Bookmarks("\Section").Range.Copy

'Create a new document to paste text from clipboard.
Documents.Add
Selection.Paste

' Removes the break that is copied at the end of the section, if any.
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1

ChangeFileOpenDirectory "C:\"
DocNum = DocNum + 1
ActiveDocument.SaveAs FileName:="test_" & DocNum & ".doc"
ActiveDocument.Close
' Move the selection to the next section in the document
Application.Browser.Next
Next i
ActiveDocument.Close savechanges:=wdDoNotSaveChanges
End Sub

 

[Doug Robbins - Word MVP on news.microsoft.com]

Change Sub BreakOnSection() to Function BreakOnSection() and assuming that
it is in a module that is in the same template, the Call BreakOnSection
should work.

Please do not post the same question separately to multiple newsgroups as
you have done.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[GeoffG]

You say that your code is not running and no error message is
being displayed.

The following two things would generate those symptoms:

(1) You have not used "Option Explicit" at the top of your code
module.

And:

(2) You have written your code in a standard module, not the
code module behind UserForm1.

The effect of (1) is you don't have to declare variables, like
"I" and "DocNum". The danger is that the VBA compiler now thinks
that "purchaser" is an undeclared variable and just goes ahead
and declares it as a variable, when, in fact, "purchaser" is a
TextBox on the form.

The effect of (2) is, when you click the command button, nothing
happens. For something to happen, you need a "click" event
procedure in the code module behind UserForm1. If your "event"
procedure is in a standard module, the command button knows
nothing about it.

If the above describes your situation, then take the following
action:

1. Open the template as a template.
2. Press ALT-F11 to open the VBA editor.
3. Display the Project Explorer windows (CTRL-R).
4. In the Project Explorer window, double-click UserForm1,
which is under under Forms, under "TemplateProject".
5. Do you see your code in the code window?
6. If not, find your code by double-clicking the other modules
listed in Project Explorer. For example, double-click "Module1"
(if you have a "Modules" folder), or double-click "ThisDocument"
under "Microsoft Word Objects". Cut your code from wherever it
is and paste it into the module behind UserForm1.
7. Go to the top of the code behind UserForm1 and insert
"Option Explicit" (see my example code below). This will force
you to declare your variables (see also my example below).
8. With any code window displayed, open the Debug menu and
select Compile TemplateProject. If you haven't declared all
variables or, if you've referred to controls that don't exist on
the form, a compile error will result. Fix any errors.
9. To make sure that "Option Explicit" is always at the top of
every code module you create in future, open the Tools menu in
the VBA editor, select Options, and select "Require Variable
Declaration".

Below is your code, which I've tested and it works perfectly.

Notice, my changes, however:

(1) I use "Me.txtPurchaser" and "Me.txtPurchaser2" in
CommandButton1_Click(). "Me" refers to UserForm1 because the
code is now behind UserForm1. If the code were not behind a
form, "Me" would generate an error, so it's good practice to use
"Me" when referring to TextBoxes (or any other control) on a
form. I renamed your textboxes with the prefix "txt"; this
makes it clear in the code window that you're referring to a
TextBox on a form, not any other type of control.

(2) I declare explicitly "I" and "DocNum" in the
BreakOnSection() procedure.


' CODE BEGINS

' The next code line must appear
' at the top of the code module:
Option Explicit

Private Sub CommandButton1_Click()

' This is the Click event procedure for
' CommandButton1, i.e. this code is fired
' when CommandButton1 is pressed.

With ActiveDocument
.Bookmarks("purchaser").Range.InsertBefore _
Me.txtPurchaser
.Bookmarks("purchaser2").Range.InsertBefore _
Me.txtPurchaser2
End With
' The following call works fine because
' BreakOnSection() is also in the UserForm1 module.
Call BreakOnSection
UserForm1.Hide

End Sub

Sub BreakOnSection()

Dim I As Integer
Dim DocNum As Integer

' Used to set criteria for moving through
' the document by section.
Application.Browser.Target = wdBrowseSection

' A mailmerge document ends with a section
' break next page. Subtracting one from the
' section count stop error message.
For I = 1 To ((ActiveDocument.Sections.Count) - 1)

' Select and copy the section text to the clipboard
ActiveDocument.Bookmarks("\Section").Range.Copy

' Create a new document to paste text from clipboard
Documents.Add
Selection.Paste

' Removes the break that is copied
' at the end of the section, if any.
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1

ChangeFileOpenDirectory "C:\"
DocNum = DocNum + 1
ActiveDocument.SaveAs FileName:="test_" & DocNum & ".doc"
ActiveDocument.Close
' Move the selection to the next section in the document
Application.Browser.Next
Next I
ActiveDocument.Close savechanges:=wdDoNotSaveChanges

End Sub

' CODE ENDS


Geoff.

PS - Now that I've gone to the effort of writing up this reply,
I'm very disappointed to see Doug's reply, which says you've
cross posted to other Newsgroups. The point is, if you cross
post, then you could put other people to the trouble of writing
up a reply, when, in fact, you've already had a solution in
another newsgroup. That is perceived by others as a complete
disregard for wasting their time. I hope you think it makes
sense, therefore, not to cross post. It's a simple courtesy to
other newsgroup users and it benefits you, too, because others
won't then shy away from your requests for help in future.
Geoff.







message

 

[discostu1975]

Doug, your solution worked like a charm, thanks so much. I apologize for the
cross post, chalk it up to a noob mistake.

 

[discostu1975]

Geoff, thanks for taking the time to post, I apologize for the cross
post...darn noobs eh!! Dougs post actually resolved my problem quite nicely
but I appreciate the detailed information you gave me