print button

Posted by steve on March 23, 2001 4:31 PM

Can anyone help, I have a userform pop-up on a before print command. I use some check boxes on the form to place print ranges on the excel sheet, I then concatenate all the ranges with the concatenate function and places the new range in cell I100. I then need to set the range as the print range. I then put the following code into the command button labeled print to try and print the range.
Range("I100").Name="PrintRange"
Range("PrintRange").PrintOut
The problem is that its trying to open the userform again it says form already displayed and its trying to print the original print area and the one thats linked to the userform.

thanks steve

heres part of the code that places the ranges to be concatenated into excel.

Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then
With Sheet4
Range("Q100") = "EE1:FA35"
End With
Else
With Sheet4
Range("Q100") = ""
End With
End If
End Sub

Posted by David Hawley on March 23, 2001 8:34 PM

Hi Steve

I cannot remember all of what your code does, but try this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
UserForm1.Show
End Sub

This will cancel the print that would normally have occured.

Then when you have finished with the form, have your Cancel button (or whatever) execute this code.
Unload Me

Don't use:
Me.Hide as this still leaves the form in memory but just not visible.

Dave

OzGrid Business Applications

Posted by steve on March 24, 2001 7:03 AM

Dave
it is trying to open the userform again off my print button on the userform. It says form already displayed can't show modally.

heres the code for the print button

Private Sub CommandButton1_Click()
With Sheet4

Range("I100").Name = "PrintRange"
Range("PrintRange").PrintOut
Unload Me

End With
End Sub

Posted by Dave Hawley on March 24, 2001 6:16 PM

Ok, I think I understand. The line:
Range("PrintRange").PrintOut
is causing the Before Print to fire again. Try this code in the Before Print module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
If UserForm1.Visible = False Then UserForm1.Show
End Sub

Dave

OzGrid Business Applications

Posted by steve on March 24, 2001 6:58 PM

Thanks dave, but it will not print the range thats in "I1".
I would appreciate any thoughts or ideas or different methods you may know to be able to print out multiple pages for a userform.

Thanks steve

Posted by Dave Hawley on March 24, 2001 8:05 PM

Sorry Steve, I'm trying to do too much at once here :o) Use this instead:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If UserForm1.Visible = False Then
UserForm1.Show
Cancel = True
End If

End Sub

OzGrid Business Applications

Posted by Dave Hawley on March 24, 2001 8:08 PM

To remove those horrible page breaks!

Private Sub UserForm_Terminate()
ActiveSheet.DisplayAutomaticPageBreaks = False
End Sub

Dave
OzGrid Business Applications

Posted by steve on March 24, 2001 8:14 PM

what you posted worked (have new problem command button wont print the range in "I4") no big hurry

: Dave : Thanks dave, but it will not print the range thats in "I1". I would appreciate any thoughts or ideas or different methods you may know to be able to print out multiple pages for a userform. Thanks steve
dave it won't print the range in "I1"

Private Sub CommandButton1_Click()
Dim i As Variant
i = Sheet4.Range("I1") '"cc1:dd35"

Sheet4.PageSetup.PrintArea = i

Unload Me

'End With
End Sub

Posted by steve on March 24, 2001 8:18 PM

is this code for me or someone else