Including Macro in Userform

[Mark]

I have the following code in a userform which executes when pressing the
CommandButton1:

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Mark"
.AddItem "Steve"
End With

With ComboBox2
.AddItem "Andy"
.AddItem "Bill"
.AddItem "Eric"
.AddItem "Jim"
.AddItem "Joe"
.AddItem "Lee"
.AddItem "Mark"
End With

With ComboBox3
.AddItem "Clarence"
.AddItem "Jim B"
End With
End Sub

Private Sub ComboBox1_Change()

With ComboBox1
.AddItem "Mark"
.AddItem "Steve"
End With

End Sub

Private Sub ComboBox2_Change()

With ComboBox2
.AddItem "Andy"
.AddItem "Bill"
.AddItem "Eric"
.AddItem "Jim"
.AddItem "Joe"
.AddItem "Lee"
.AddItem "Mark"
End With

End Sub
Private Sub ComboBox3_Change()

With ComboBox3
.AddItem "Clarence"
.AddItem "Jim B"
End With

End Sub
Private Sub CommandButton1_Click()

With ActiveDocument
.Bookmarks("Report_Number").Range _
.InsertBefore TextBox1
.Bookmarks("Date").Range _
.InsertBefore TextBox2
.Bookmarks("Location").Range _
.InsertBefore TextBox3
.Bookmarks("Spool_Details").Range _
.InsertBefore TextBox4
.Bookmarks("Pump_Pressure").Range _
.InsertBefore TextBox5
.Bookmarks("OPM").Range _
.InsertBefore ComboBox1
.Bookmarks("NGC").Range _
.InsertBefore ComboBox2
.Bookmarks("Ace").Range _
.InsertBefore ComboBox3
End With

UserForm1.Hide
End Sub


This works fine with a single page report when the OK button is pressed.
What I would now like to do is to add a second CommandButton which would
enter the data from the first CommandButton then execute the following macro
that I recorded to enter a page break, duplicate the page and then delete out
the original sign offs (ComboBox names) to format the two page document with
the sign off at the bottom of the second page only.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 02-Nov-06 by User
'
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveLeft Unit:=wdCell
Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=34, Extend:=wdExtend
Selection.Copy
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.InsertBreak Type:=wdPageBreak
Selection.PasteAndFormat (wdPasteDefault)
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.EndKey Unit:=wdLine
Selection.EndKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.EndKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=2
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=42
Selection.MoveDown Unit:=wdLine, Count:=2
Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend
Selection.Tables(1).Select
Selection.Tables(1).Delete
Selection.MoveUp Unit:=wdLine, Count:=37
Selection.MoveRight Unit:=wdCharacter, Count:=9
End Sub


I have tried this, but am coming to a dead end (you may have noticed, I am
very new to VB!). Any help would be much appreciated.

 

[Doug Robbins - Word MVP]

For starters, you should get rid of the ComboBox_Change events. You already
have the names in the ComboBoxes and there is no reason to duplicate them.

As for your second command button, I think you should make up your mind
whether you want one page or two pages before the first command button is
clicked, by means of a check box or something that the command button code
interprets, adding the page if required so that you only have the need for
the one command button.

Getting the design of your application sorted out is just as important, if
not more important than the coding. Of course the coding has to be correct,
but correct code will not make up for bad system design.

--
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