spreadsheet on userform

[alcorjr]

Hi guys, maybe you can help me with this.

I have a spreadsheet on a userform. When I try Printform, the form get
printed OK, but the spreadsheet on it comes out reduced so much tha
it's illegible.
This is the script I'm using;


code:
--------------------------------------------------------------------------------

Sub print_savings()
Dim prl As Long, prh As Long
prl = UserForm11.Spreadsheet1.Range("A500").End(xlUp).Row
prh = UserForm11.Spreadsheet1.Range("A1:A" & prl).Height
UserForm11.Height = prh + 488
UserForm11.Image1.Height = prh
UserForm11.Spreadsheet1.Height = prh

UserForm11.Spreadsheet1.ActiveWindow.EnableResize = True
UserForm11.Spreadsheet1.Height = prh
UserForm11.PrintForm
End Sub

--------------------------------------------------------------------------------





Any ideas

 

[Michel Pierron]

Hi "alcorjr"; you can try this:

Private Sub CmdPrint_Click()
Me.Repaint
Const Wbk = "c:\Mes Documents\MyTest.xls" ' for example !
Me.Spreadsheet1.Export Wbk, ssExportActionNone, ssExportAsAppropriate
Dim oXL As Object, wBook As Object
Set oXL = CreateObject("Excel.Application")
Set wBook = oXL.Workbooks.Open(Wbk)
wBook.Sheets(1).PrintOut Copies:=1
wBook.Close False
Kill Wbk
oXL.Quit
Set wBook = Nothing: Set oXL = Nothing
End Sub

 

[alcorjr]

Michel, thanks for your quick reply, but in the end I decided that,
since I didn't really need to use the special features provided by the
spreadsheet, I took the easy way out and substitued it for a
Listbox.

I'll save your code for future use, though.

Cheers, and Merry Xmas!!!