Making a Data Form to allow me to print different worksheets

[Bob Reynolds]

I have a workbook with about 41 worksheets in it. I have a need to use a
pull down menu or data form or anything that will work, to allow me to
select any one of the worksheets or all of them or any combination of them
to print. The user would select which to print with out having to go
individually by tab by tab.
Any help would be appreciated.]
Thanks
Bob Reynolds

 

[Dave Smith]

One way that comes quickly to mind is this:

Create a new sheet named PrintSelector and hide it. Add these macros and
set appropriate shortcut keys:

Sub SelectSheets()
Dim ws As Worksheet
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Clear
ActiveSheet.Cells.Locked = True
Range("A1").Select
For Each ws In Sheets
selection.Value = ws.Name
selection.Offset(1, 0).Select
Next
ActiveSheet.Protect
End Sub

Sub PrintSelectedSheets()
Dim aCell As Range
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
For Each aCell In selection
If Not IsEmpty(aCell.Value) Then
Sheets(aCell.Value).PrintPreview
End If
Next
ActiveSheet.Visible = False
End Sub


The first macro will unhide the PrintSelector sheet and create a list of the
sheet names and protect the sheet so your user can't change it. Then your
user can indicate which sheets to print by simply selecting the names of the
sheets (use Control and Shift to multi-select) and then calling the second
macro which will print the sheets and hide the selector list sheet. If you
want you can bind the second macro to a button on the sheet.

HTH
Dave Smith

 

[Dave Smith]

Almost forgot. For testing, I used PrintPreview. You might want to change
this to PrintOut.

One way that comes quickly to mind is this:

Create a new sheet named PrintSelector and hide it. Add these macros and
set appropriate shortcut keys:

Sub SelectSheets()
Dim ws As Worksheet
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Clear
ActiveSheet.Cells.Locked = True
Range("A1").Select
For Each ws In Sheets
selection.Value = ws.Name
selection.Offset(1, 0).Select
Next
ActiveSheet.Protect
End Sub

Sub PrintSelectedSheets()
Dim aCell As Range
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
For Each aCell In selection
If Not IsEmpty(aCell.Value) Then
Sheets(aCell.Value).PrintPreview
End If
Next
ActiveSheet.Visible = False
End Sub


The first macro will unhide the PrintSelector sheet and create a list of the
sheet names and protect the sheet so your user can't change it. Then your
user can indicate which sheets to print by simply selecting the names of the
sheets (use Control and Shift to multi-select) and then calling the second
macro which will print the sheets and hide the selector list sheet. If you
want you can bind the second macro to a button on the sheet.

HTH
Dave Smith

I have a workbook with about 41 worksheets in it. I have a need to use a
pull down menu or data form or anything that will work, to allow me to
select any one of the worksheets or all of them or any combination of them
to print. The user would select which to print with out having to go
individually by tab by tab.
Any help would be appreciated.]
Thanks
Bob Reynolds

 

[Bob Reynolds]

Works like a charm, thanks so much..

Bob

One way that comes quickly to mind is this:

Create a new sheet named PrintSelector and hide it. Add these macros and
set appropriate shortcut keys:

Sub SelectSheets()
Dim ws As Worksheet
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Clear
ActiveSheet.Cells.Locked = True
Range("A1").Select
For Each ws In Sheets
selection.Value = ws.Name
selection.Offset(1, 0).Select
Next
ActiveSheet.Protect
End Sub

Sub PrintSelectedSheets()
Dim aCell As Range
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
For Each aCell In selection
If Not IsEmpty(aCell.Value) Then
Sheets(aCell.Value).PrintPreview
End If
Next
ActiveSheet.Visible = False
End Sub


The first macro will unhide the PrintSelector sheet and create a list of the
sheet names and protect the sheet so your user can't change it. Then your
user can indicate which sheets to print by simply selecting the names of the
sheets (use Control and Shift to multi-select) and then calling the second
macro which will print the sheets and hide the selector list sheet. If you
want you can bind the second macro to a button on the sheet.

HTH
Dave Smith

I have a workbook with about 41 worksheets in it. I have a need to use a
pull down menu or data form or anything that will work, to allow me to
select any one of the worksheets or all of them or any combination of them
to print. The user would select which to print with out having to go
individually by tab by tab.
Any help would be appreciated.]
Thanks
Bob Reynolds

 

[Bob Reynolds]

I get this error now evertime I use the macro for the printing. I can send
two pages to print preview andthen this error comes up.

Microsoft Visual Basic Box
Run-time error '1004':
PrintPreview method of Worksheet class failed.

Any ideas, I can email the workbook if needed.
Thanks
Bob

One way that comes quickly to mind is this:

Create a new sheet named PrintSelector and hide it. Add these macros and
set appropriate shortcut keys:

Sub SelectSheets()
Dim ws As Worksheet
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Clear
ActiveSheet.Cells.Locked = True
Range("A1").Select
For Each ws In Sheets
selection.Value = ws.Name
selection.Offset(1, 0).Select
Next
ActiveSheet.Protect
End Sub

Sub PrintSelectedSheets()
Dim aCell As Range
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
For Each aCell In selection
If Not IsEmpty(aCell.Value) Then
Sheets(aCell.Value).PrintPreview
End If
Next
ActiveSheet.Visible = False
End Sub


The first macro will unhide the PrintSelector sheet and create a list of the
sheet names and protect the sheet so your user can't change it. Then your
user can indicate which sheets to print by simply selecting the names of the
sheets (use Control and Shift to multi-select) and then calling the second
macro which will print the sheets and hide the selector list sheet. If you
want you can bind the second macro to a button on the sheet.

HTH
Dave Smith

I have a workbook with about 41 worksheets in it. I have a need to use a
pull down menu or data form or anything that will work, to allow me to
select any one of the worksheets or all of them or any combination of them
to print. The user would select which to print with out having to go
individually by tab by tab.
Any help would be appreciated.]
Thanks
Bob Reynolds

 

[GorillaBoze]

I am a newbie here...but need something like this on a spreadsheet
created. There are only 20 worksheets in this spreadsheet, but I woul
like to be able to select the ones I want to print, or print them all.
I have a worksheet that is called "MENU" that has some buttons wit
macros, and I would like the print menu on the main menu worksheet.
Can you explain in a little more detail the below macros and where
should put them??

Thanks

*One way that comes quickly to mind is this:

Create a new sheet named PrintSelector and hide it. Add these macro
and
set appropriate shortcut keys:

Sub SelectSheets()
Dim ws As Worksheet
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Clear
ActiveSheet.Cells.Locked = True
Range("A1").Select
For Each ws In Sheets
selection.Value = ws.Name
selection.Offset(1, 0).Select
Next
ActiveSheet.Protect
End Sub

Sub PrintSelectedSheets()
Dim aCell As Range
Sheets("PrintSelector").Visible = True
Sheets("PrintSelector").Activate
For Each aCell In selection
If Not IsEmpty(aCell.Value) Then
Sheets(aCell.Value).PrintPreview
End If
Next
ActiveSheet.Visible = False
End Sub


The first macro will unhide the PrintSelector sheet and create a lis
of the
sheet names and protect the sheet so your user can't change it. The
your
user can indicate which sheets to print by simply selecting the name
of the
sheets (use Control and Shift to multi-select) and then calling th
second
macro which will print the sheets and hide the selector list sheet.
If you
want you can bind the second macro to a button on the sheet.

HTH
Dave Smith

I have a workbook with about 41 worksheets in it. I have a need t use a
pull down menu or data form or anything that will work, to allow m to
select any one of the worksheets or all of them or any combinatio of them
to print. The user would select which to print with out having t go
individually by tab by tab.
Any help would be appreciated.]
Thanks
Bob Reynolds

 

[GorillaBoze]

Ok...I got it to work (so I'm not as dumb as I thought I was).

I have 2 worksheets that I don't want to show up on the printselection
tab. How can I do this?