Problems with simple print macros

Hi all,

I've tried to create (i.e. cut and paste several macros from
public.excel.programming) several print macros for my workbooks. One
has been designed to print two sheets only, "total" and "monthly" and
the other has been designed to print all other sheets other than the
aforementioned two and hidden worksheets. An option I'd like to add to
both is to allow the user to select whether he/she wants to print in
color or in grayscale, but I seem to be unable to do this. For
example, macro1 is:

Private Sub commandbutton1_click()
Sheets(Array("Total", "Monthly")).Select
PageSetup.BlackAndWhite = _
MsgBox("Do you want to print in color?", _
vbYesNo, "Print Options") = vbNo
Application.Dialogs(xlDialogPrint).Show
Sheets("Total").PageSetup.BlackAndWhite = False
Sheets("Monthly").PageSetup.BlackAndWhite = False
End Sub

This currently prints the "Total" sheet as the user specified, but
always prints "Monthly" in color. I assume I'm not selecting both
sheets correctly but I can't seem to get it to work.

I'd also like to add this option to the second macro, but I can seem to
be able to place it within the macro correctly so it prints out all
selected sheets as specified.

Private Sub CommandButton2_Click()
Dim Sheet As Worksheet
Dim lAnswer As Long
Dim lSheet As Long
Dim sh As Worksheet
Dim arySheets
lAnswer = MsgBox("This report contains " & Sheets.Count - 10 & _
" sheets - Do you want to print them all?", vbYesNo, "Print?")
If lAnswer = vbNo Then
Exit Sub
Else
ReDim arySheets(1 To 1)
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Total" And sh.Name <> "Monthly" And sh.Visible =
xlSheetVisible Then
lSheet = lSheet + 1
ReDim Preserve arySheets(1 To lSheet)
arySheets(lSheet) = sh.Name
End If
Next sh
Worksheets(arySheets).Select
Application.Dialogs(xlDialogPrint).Show
Sheets("Total").Select
End If
End Sub

Thanks to anyone who can offer any help.
B.Hurness

In general, you have to set up pagesetup options on each individual sheet.
So you would have to loop through the selected sheets


--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I've tried to create (i.e. cut and paste several macros from
> public.excel.programming) several print macros for my workbooks. One
> has been designed to print two sheets only, "total" and "monthly" and
> the other has been designed to print all other sheets other than the
> aforementioned two and hidden worksheets. An option I'd like to add to
> both is to allow the user to select whether he/she wants to print in
> color or in grayscale, but I seem to be unable to do this. For
> example, macro1 is:
>
> Private Sub commandbutton1_click()
> Sheets(Array("Total", "Monthly")).Select
> PageSetup.BlackAndWhite = _
> MsgBox("Do you want to print in color?", _
> vbYesNo, "Print Options") = vbNo
> Application.Dialogs(xlDialogPrint).Show
> Sheets("Total").PageSetup.BlackAndWhite = False
> Sheets("Monthly").PageSetup.BlackAndWhite = False
> End Sub
>
> This currently prints the "Total" sheet as the user specified, but
> always prints "Monthly" in color. I assume I'm not selecting both
> sheets correctly but I can't seem to get it to work.
>
> I'd also like to add this option to the second macro, but I can seem to
> be able to place it within the macro correctly so it prints out all
> selected sheets as specified.
>
> Private Sub CommandButton2_Click()
> Dim Sheet As Worksheet
> Dim lAnswer As Long
> Dim lSheet As Long
> Dim sh As Worksheet
> Dim arySheets
> lAnswer = MsgBox("This report contains " & Sheets.Count - 10 & _
> " sheets - Do you want to print them all?", vbYesNo, "Print?")
> If lAnswer = vbNo Then
> Exit Sub
> Else
> ReDim arySheets(1 To 1)
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> "Total" And sh.Name <> "Monthly" And sh.Visible =
> xlSheetVisible Then
> lSheet = lSheet + 1
> ReDim Preserve arySheets(1 To lSheet)
> arySheets(lSheet) = sh.Name
> End If
> Next sh
> Worksheets(arySheets).Select
> Application.Dialogs(xlDialogPrint).Show
> Sheets("Total").Select
> End If
> End Sub
>
> Thanks to anyone who can offer any help.
> B.Hurness
>