printing using vba; listbox/command button

[bkey01]

I have an Excel workbook containing over 40 sheets with each having a
least 7 pages setup to print out in the page setup area.

I have multiple listboxes setup to goto specific cell that contain th
start of each page.

I am trying to write a code in vb using a listbox and command button t
print certain pages from different sheets. i.e. On sheet 1, I want t
print the information that starts in cell a50 and ends in aa110 an
then on sheet2, I want to print the information that starts in cell a1
and ends in aa32 and so on.

Example of code I have tried:
if listbox1.selected(0) then goto errhandler1
listbos1.selected(0) = false

errhandler1:
sheet1.range(a50:aa110).printout
end sub

THIS DID NOT WORK FOR ME!

Can someone please help me with this.

Thanks in advance

 

[Ron de Bruin]

Please don't post in more then one group

 

[Tim]

I hope this helps...

Frank helped me sort out the bugs in this code and it will do, from
your message says, what you need it to do.

Paste this into a new module and assign a command button that is
located some where in your workbook.
When you click the command button a new dialog box (with check boxes)
is created.
The check boxes are labeled with all the sheets and pages in your work
book. You simply check the boxs you want to print and watch it go to
work.
Note: If your sheets are hidden or very hidden they will not show up
in the dialog box.
Regards
Tim

Option Explicit

Sub SelectSheetPrint()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim CB As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible = xlSheetVisible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each CB In PrintDlg.CheckBoxes
If CB.Value = xlOn Then
Worksheets(CB.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next CB
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
Cover.Activate
End Sub