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

Problems with simple print macros
 

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


wrote in message
oups.com...
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