U
Ugleeduck
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
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