Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Multiple Sheets in VBA | Excel Worksheet Functions | |||
selecting multiple sheets | Excel Worksheet Functions | |||
Page set-up when selecting multiple sheets | Excel Programming | |||
selecting multiple sheets | Excel Programming | |||
Changing the value in multiple sheets without selecting those sheets | Excel Programming |