Hello everyone,

Thanks in advance for reading my post. I'm very new to Excel VBA
programming and having some problem with a code snippet that prints
selected sheets.

I have a form with two list boxes. The left one shows the all the
worksheets and the user can add any or all of these sheets to the list
box on the right. Then I pass the list from the second list box to a
sub procedure taht prints. It prints the selected worksheets, but
everytime I get an error saying " Object variable or with block
variable not set". Here's my code...


Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing
Dim objXLSApp As Object
Dim wbkXLSBook As Workbook
Set objXLSApp = GetObject(, "Excel.Application")
Set wbkXLSBook = objXLSApp.ThisWorkbook

With wbkXLSBook

For i = 0 To lstPrintQue.ListCount - 1
wbkXLSBook.Sheets(lstPrintQue.List(i)).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i

End With

'release object
Set objXLSApp = Nothing

Unload Me


End Sub



I created an Excel appliocation object and then added the selected
worksheets to that application and then printed them out. This looks a
round about way of doing something as simple as printing a few sheets.
Is there a better way? If somebody code show me a code sample I'd
greatly appreciate it. Or, maybe some of you can suggest a few changes
to my existing code?

Again, I really appreciate the help.

Thanks!

A. Rahman

You don't need to use createobject or getobject. Application already refers
to the instance of excel with your workbook and thisworkbook is already
defined.

Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing

With ThisWorkbook

For i = 0 To lstPrintQue.ListCount - 1
if i = 0 then
.Sheets(lstPrintQue.list(i)).Select
else
.Sheets(ListPrintQue.List(i)).Select False
end if
Next i
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
.Worksheets(1).Select
End With
Unload Me
End Sub

--
Regards,
Tom Ogilvy


"Ugleeduck" wrote in message
om...
Hello everyone,

Thanks in advance for reading my post. I'm very new to Excel VBA
programming and having some problem with a code snippet that prints
selected sheets.

I have a form with two list boxes. The left one shows the all the
worksheets and the user can add any or all of these sheets to the list
box on the right. Then I pass the list from the second list box to a
sub procedure taht prints. It prints the selected worksheets, but
everytime I get an error saying " Object variable or with block
variable not set". Here's my code...


Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing
Dim objXLSApp As Object
Dim wbkXLSBook As Workbook
Set objXLSApp = GetObject(, "Excel.Application")
Set wbkXLSBook = objXLSApp.ThisWorkbook

With wbkXLSBook

For i = 0 To lstPrintQue.ListCount - 1
wbkXLSBook.Sheets(lstPrintQue.List(i)).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i

End With

'release object
Set objXLSApp = Nothing

Unload Me


End Sub



I created an Excel appliocation object and then added the selected
worksheets to that application and then printed them out. This looks a
round about way of doing something as simple as printing a few sheets.
Is there a better way? If somebody code show me a code sample I'd
greatly appreciate it. Or, maybe some of you can suggest a few changes
to my existing code?

Again, I really appreciate the help.

Thanks!

A. Rahman

It works fine now. Thank you so much for all your help and patience!

"Tom Ogilvy" wrote in message ...
there was a typo where lstprintque was spelled listprintque in the second
leg of the if statement.

I ran this in excel and it caused no error. The code is in the Userform
module (so ME refers to the userform).

Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing
Unload Me '<== move here to prevent interference
' with printpreview which I used to save paper
ThisWorkbook.Activate
With ThisWorkbook

For i = 0 To lstPrintQue.ListCount - 1
If i = 0 Then
.Sheets(lstPrintQue.List(i)).Select
Else
.Sheets(lstPrintQue.List(i)).Select False
End If
Next i
ActiveWindow.SelectedSheets.PrintPreview
.Worksheets(1).Select
End With

End Sub


I don't think it is anything in this code that is causing your problem.

Regards,
Tom Ogilvy

Ugleeduck wrote in message
m...
Thanks for the help. I tried the code. The result is same. It prints ,
but also gives me the error "Object variable or with block variable
not set". Just like before :(



"Tom Ogilvy" wrote in message
...
You don't need to use createobject or getobject. Application already
refers
to the instance of excel with your workbook and thisworkbook is already
defined.

Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing

With ThisWorkbook

For i = 0 To lstPrintQue.ListCount - 1
if i = 0 then
.Sheets(lstPrintQue.list(i)).Select
else
.Sheets(ListPrintQue.List(i)).Select False
end if
Next i
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
.Worksheets(1).Select
End With
Unload Me
End Sub

--
Regards,
Tom Ogilvy


"Ugleeduck" wrote in message
om...
Hello everyone,

Thanks in advance for reading my post. I'm very new to Excel VBA
programming and having some problem with a code snippet that prints
selected sheets.

I have a form with two list boxes. The left one shows the all the
worksheets and the user can add any or all of these sheets to the list
box on the right. Then I pass the list from the second list box to a
sub procedure taht prints. It prints the selected worksheets, but
everytime I get an error saying " Object variable or with block
variable not set". Here's my code...


Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing
Dim objXLSApp As Object
Dim wbkXLSBook As Workbook
Set objXLSApp = GetObject(, "Excel.Application")
Set wbkXLSBook = objXLSApp.ThisWorkbook

With wbkXLSBook

For i = 0 To lstPrintQue.ListCount - 1
wbkXLSBook.Sheets(lstPrintQue.List(i)).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i

End With

'release object
Set objXLSApp = Nothing

Unload Me


End Sub



I created an Excel appliocation object and then added the selected
worksheets to that application and then printed them out. This looks a
round about way of doing something as simple as printing a few sheets.
Is there a better way? If somebody code show me a code sample I'd
greatly appreciate it. Or, maybe some of you can suggest a few changes
to my existing code?

Again, I really appreciate the help.

Thanks!

A. Rahman