bryVA
05-08-2009, 10:28 AM
Hello all,
I have setup a userform in Excel 2000 that allows the user to click on a checkbox to select which page of a word document they want to print. The following is my code"
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim strFPath As String
Dim oWord As Object
Dim oDoc As Document
strFPath = ThisWorkbook.Path & Application.PathSeparator
On Error Resume Next
Set oWord = GetObject(Class:="Word.Application")
With oWord
.Application.Visible = False
Set oDoc = .Documents.Add(Template:=strFPath & "FNOD docs.dot")
End With
Set objword = CreateObject("Word.Application")
On Error Resume Next
Dim e1, e2, e3, e4, e5, e6, e7, e8, e9 As Integer
Dim c1, c2, c3, c4, c5, c6, c7, c8 As String
Dim epage As String
e1 = Empty
e2 = Empty
e3 = Empty
e4 = Empty
e5 = Empty
e6 = Empty
e7 = Empty
e8 = Empty
e9 = Empty
c1 = ""
c2 = ""
c3 = ""
c4 = ""
c5 = ""
c6 = ""
c7 = ""
c8 = ""
If CheckBox1 = True Then e1 = "1"
If CheckBox2 = True Then e2 = "2"
If CheckBox3 = True Then e3 = "3"
If CheckBox4 = True Then e4 = "4"
If CheckBox5 = True Then e5 = "5"
If CheckBox6 = True Then e6 = "6"
If CheckBox7 = True Then e7 = "7"
If CheckBox8 = True Then e8 = "8"
If CheckBox9 = True Then e9 = "9"
If CheckBox1 = True Then c1 = ", "
If CheckBox2 = True Then c2 = ", "
If CheckBox3 = True Then c3 = ", "
If CheckBox4 = True Then c4 = ", "
If CheckBox5 = True Then c5 = ", "
If CheckBox6 = True Then c6 = ", "
If CheckBox7 = True Then c7 = ", "
If CheckBox8 = True Then c8 = ", "
epage = e1 & c1 & e2 & c2 & e3 & c3 & e4 & c4 & e5 & c5 & e6 & c6 & e7 & c7 & e8 & c8 & e9
On Error GoTo errhandler
With Dialogs(wdDialogFilePrint)
.Range = wdPrintRangeOfPages
.Pages = epage
.Execute
End With
With oDoc
.Close
End With
With oDoc
.Close
End With
oWord.Close
Unload Me
Exit Sub
errhandler:
MsgBox ("A error occurred. Please manually print the needed documentation.")
End Sub
I can't get this to work. I orginially had it in a word document module which it opened the other word document and printed the pages. It worked but when I have andjusted it for excel it doesn't work. What can I do to correct this? I know that there is a better way to do this but I diffenently don't know VBA as well as I would like to. Please educate me.
Thanks all,
Bryan
I have setup a userform in Excel 2000 that allows the user to click on a checkbox to select which page of a word document they want to print. The following is my code"
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim strFPath As String
Dim oWord As Object
Dim oDoc As Document
strFPath = ThisWorkbook.Path & Application.PathSeparator
On Error Resume Next
Set oWord = GetObject(Class:="Word.Application")
With oWord
.Application.Visible = False
Set oDoc = .Documents.Add(Template:=strFPath & "FNOD docs.dot")
End With
Set objword = CreateObject("Word.Application")
On Error Resume Next
Dim e1, e2, e3, e4, e5, e6, e7, e8, e9 As Integer
Dim c1, c2, c3, c4, c5, c6, c7, c8 As String
Dim epage As String
e1 = Empty
e2 = Empty
e3 = Empty
e4 = Empty
e5 = Empty
e6 = Empty
e7 = Empty
e8 = Empty
e9 = Empty
c1 = ""
c2 = ""
c3 = ""
c4 = ""
c5 = ""
c6 = ""
c7 = ""
c8 = ""
If CheckBox1 = True Then e1 = "1"
If CheckBox2 = True Then e2 = "2"
If CheckBox3 = True Then e3 = "3"
If CheckBox4 = True Then e4 = "4"
If CheckBox5 = True Then e5 = "5"
If CheckBox6 = True Then e6 = "6"
If CheckBox7 = True Then e7 = "7"
If CheckBox8 = True Then e8 = "8"
If CheckBox9 = True Then e9 = "9"
If CheckBox1 = True Then c1 = ", "
If CheckBox2 = True Then c2 = ", "
If CheckBox3 = True Then c3 = ", "
If CheckBox4 = True Then c4 = ", "
If CheckBox5 = True Then c5 = ", "
If CheckBox6 = True Then c6 = ", "
If CheckBox7 = True Then c7 = ", "
If CheckBox8 = True Then c8 = ", "
epage = e1 & c1 & e2 & c2 & e3 & c3 & e4 & c4 & e5 & c5 & e6 & c6 & e7 & c7 & e8 & c8 & e9
On Error GoTo errhandler
With Dialogs(wdDialogFilePrint)
.Range = wdPrintRangeOfPages
.Pages = epage
.Execute
End With
With oDoc
.Close
End With
With oDoc
.Close
End With
oWord.Close
Unload Me
Exit Sub
errhandler:
MsgBox ("A error occurred. Please manually print the needed documentation.")
End Sub
I can't get this to work. I orginially had it in a word document module which it opened the other word document and printed the pages. It worked but when I have andjusted it for excel it doesn't work. What can I do to correct this? I know that there is a better way to do this but I diffenently don't know VBA as well as I would like to. Please educate me.
Thanks all,
Bryan