selecting multiple sheets
 

I am trying to select multiple sheets according to whether
they have any information on them. Ithen want to use the
printdialog box so that the individual can select his
printer.
I have figured out how to close the unnecessary sheets so
if I could get the "entire workbook" radio button to be
clicked upon opening the dialogs box that would work. I
looked through all of the arguments and I see a print_what
argument is available but I cannot seem to figure out what
words to use.
I can make if statements and find the sheets, but the only
way I can get the sheets to print is with Sheets(Array
(?)).Select. I cannot figure out how to change the array
depending on whether I want to select that sheet or not.
Please help if you can.
Dennis

selecting multiple sheets
 

Dennis,

Try this code:

Sub testit()
Dim arr() As Variant, blnResult As Boolean

ReDim arr(1)
arr(0) = "Sheet1"
arr(1) = "Sheet2"
Sheets(arr).Select

blnResult = Application.Dialogs( _
xlDialogPrint).Show(, , , , , , , , , , , 2)
End Sub


I also reference Tom Ogilvy's post about how to show the Print Dialog
(something I never knew about!)
http://groups.google.co.nz/groups?hl...%40tkmsftngp02



"Dennis" wrote in message
...
I am trying to select multiple sheets according to whether
they have any information on them. Ithen want to use the
printdialog box so that the individual can select his
printer.
I have figured out how to close the unnecessary sheets so
if I could get the "entire workbook" radio button to be
clicked upon opening the dialogs box that would work. I
looked through all of the arguments and I see a print_what
argument is available but I cannot seem to figure out what
words to use.
I can make if statements and find the sheets, but the only
way I can get the sheets to print is with Sheets(Array
(?)).Select. I cannot figure out how to change the array
depending on whether I want to select that sheet or not.
Please help if you can.
Dennis

selecting multiple sheets
 

Once you get the sheets selected/chosen, why not just print it (well, if you
don't have to let the user change printers...)

You can do it by selecting sheets:

Option Explicit
Sub testme01()

Dim AlreadySelected As Boolean
Dim wks As Worksheet

AlreadySelected = False
For Each wks In Worksheets
If wks.Range("a1").Value = 21 Then
wks.Select Not AlreadySelected
AlreadySelected = True
End If
Next wks

If AlreadySelected = False Then
MsgBox "nothing selected"
Else
ActiveWindow.SelectedSheets.PrintPreview '.printout
End If

End Sub


Or you could just keep track of the sheets yourself (and not select them):

Option Explicit
Sub testme02()

Dim mySheetNames() As String
Dim wksCtr As Long
Dim iCtr As Long

iCtr = 0
For wksCtr = 1 To Worksheets.Count
If Worksheets(wksCtr).Range("a1").Value = 1 Then
iCtr = iCtr + 1
ReDim Preserve mySheetNames(1 To iCtr)
mySheetNames(iCtr) = Worksheets(wksCtr).Name
End If
Next wksCtr

If iCtr = 0 Then
MsgBox "nothing selected"
Else
Worksheets(mySheetNames).PrintPreview '.printout
End If

End Sub


Dennis wrote:

I am trying to select multiple sheets according to whether
they have any information on them. Ithen want to use the
printdialog box so that the individual can select his
printer.
I have figured out how to close the unnecessary sheets so
if I could get the "entire workbook" radio button to be
clicked upon opening the dialogs box that would work. I
looked through all of the arguments and I see a print_what
argument is available but I cannot seem to figure out what
words to use.
I can make if statements and find the sheets, but the only
way I can get the sheets to print is with Sheets(Array
(?)).Select. I cannot figure out how to change the array
depending on whether I want to select that sheet or not.
Please help if you can.
Dennis

--

Dave Peterson