Print Selection in Code

  • Thread starter Thread starter Donna Brooks
  • Start date Start date
D

Donna Brooks

When a user prints my report, I need to only print a
certain range. This is the code I used for the print
command button. It is supposed to display an input box to
input the no. of copies, then only print out the
selection that is in the code. When I run this code, I
get a run-time error '438 (Object doesn't support this
property or method.),if you click end on this error, the
report prints out fine, you click "Debug", you get this.
Run-time error'-2147417878(80010108). Automation error
The object invoked has disconnected from it's clients,
excel locks up recovers my document, sends an error
report and closes. Does anyone have a fix or a better way
to print just that range? Please help. Here is my code.

Private Sub cmdPrintAtt_Click()
Dim NoCopies As String
Dim Message, Title, Default, MyValue
Message = "How many copies?" ' Set prompt.
Title = "Print" ' Set title.
Default = "1" ' Set default.
' Display message, title, and default value.
NoCopies = InputBox(Message, Title, Default)
Range("A1:AH1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PrintOut Copies:=NoCopies
End Sub

TIA,
Donna Brooks
 
I can't replicate the problem, but perhaps using the range object
directly rather than the Selection object will solve it:

Private Sub cmdPrintAtt_Click()
Const MESSAGE As String = "How many copies?"
Const TITLE As String = "Print"
Const DEFAULT As Integer = 1
Dim NoCopies As String
NoCopies = InputBox(MESSAGE, TITLE, DEFAULT)
With Range("A1:AH1")
Range(.Cells, .Cells.End(xlDown)).PrintOut _
Copies:=NoCopies
End With
End Sub
 
Change the takefocusonclick in the properties of the button
to false and it is working(Bug in 97)
 
Back
Top