CheckBox to select pages to print?

  • Thread starter Thread starter Dave Peterson
  • Start date Start date
D

Dave Peterson

One way:

Option Explicit
Sub testme()

Dim SheetList(1 To 6) As String
Dim sCtr As Long

sCtr = 0
If PhaseCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "131 Phase1"
sCtr = sCtr + 1
SheetList(sCtr) = "131 Phase2"
End If

If ShopCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "131 Shop1"
sCtr = sCtr + 1
SheetList(sCtr) = "131 Shop2"
End If

If RFCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "RF"
End If

If StatusCheckbox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "Status"
End If

If sCtr = 0 Then
'all the checkboxes were false
MsgBox "Nothing to print"
Else
'get rid of any unused elements in the array
ReDim Preserve SheetList(1 To sCtr)
Sheets(SheetList).PrintOut Copies:=1, Collate:=True
End If

End Sub

(untested)
 
I am assuming your "Then" statements are the sheet names. Try something
similar to:

If PhaseCheckBox.Value = True Then Sheets("131 Phase1").select false
If PhaseCheckBox.Value = True Then sheets( "131 Phase2").select false
........
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


Using Select with False will group the sheet with the current active sheet.
You may need to adjust to deal with the current active sheet or remove false
from the first line.




--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


I tried this but when the varible is empty it calses an error. I can't
figure out any way around this. Thanks for any help.

Dim Phase1, Phase2, Shop1, Shop2, RF, Status

If PhaseCheckBox.Value = True Then Phase1 = "131 Phase1" Else Phase1 = ""
If PhaseCheckBox.Value = True Then Phase2 = "131 Phase2" Else Phase2 = ""
If ShopCheckBox.Value = True Then Shop1 = "131 Shop1" Else Shop1 = ""
If ShopCheckBox.Value = True Then Shop2 = "131 Shop2" Else Shop2 = ""
If RFCheckBox.Value = True Then RF = "RF Sheet" Else RF = ""
If StatusCheckBox.Value = True Then Status = "STATUS" Else Status ""

Sheets (Array(Phase1, Phase2, Shop1, Shop2, RF, Status)).select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
I tried this but when the varible is empty it calses an error. I can't
figure out any way around this. Thanks for any help.

Dim Phase1, Phase2, Shop1, Shop2, RF, Status

If PhaseCheckBox.Value = True Then Phase1 = "131 Phase1" Else Phase1 = ""
If PhaseCheckBox.Value = True Then Phase2 = "131 Phase2" Else Phase2 = ""
If ShopCheckBox.Value = True Then Shop1 = "131 Shop1" Else Shop1 = ""
If ShopCheckBox.Value = True Then Shop2 = "131 Shop2" Else Shop2 = ""
If RFCheckBox.Value = True Then RF = "RF Sheet" Else RF = ""
If StatusCheckBox.Value = True Then Status = "STATUS" Else Status ""

Sheets (Array(Phase1, Phase2, Shop1, Shop2, RF, Status)).select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
Yep. That was a mistake that would have been caught if I tested.

This version I tested.

Option Explicit
Sub PrintButton_Click()

Dim SheetList() As String
ReDim SheetList(1 To 6) '6 is the maximum sheets you care about.
Dim sCtr As Long
Dim resp As Long

resp = MsgBox("The name of the active printer is " _
& Application.ActivePrinter & vbLf & vbLf _
& "Do you want to print?", vbYesNo) ' Printing

If resp = vbNo Then Exit Sub

sCtr = 0
If PhaseCheckbox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "131 Phase1"
sCtr = sCtr + 1
SheetList(sCtr) = "131 Phase2"
End If

If ShopCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "131 Shop1"
sCtr = sCtr + 1
SheetList(sCtr) = "131 Shop2"
End If

If RFCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "RF"
End If

If StatusCheckbox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "Status"
End If

If sCtr = 0 Then
'all the checkboxes were false
MsgBox "No Pages Checked", , "Error"
Else
'get rid of any unused elements in the array
ReDim Preserve SheetList(1 To sCtr)
Me.Hide
Sheets(SheetList).PrintOut Copies:=1, Collate:=True, Preview:=True
Me.Show
End If

End Sub

I added Preview:=true to save some paper when testing.

When you're ready to turn it on for real paper, remove the me.Hide and the
me.show lines and the preview:=true parm from the .printout line.
 
I inserted the code and I get Compile error: " Array already dimensioned"
on - ReDim Preserve SheetList(1 To sCtr)

This is my code :

Sub PrintButton_Click()

MsgBox "The name of the active printer is " & Application.ActivePrinter & "
do you want to print?", vbYesNo ' Printing
If vbYes Then GoTo Printit
If vbNo Then GoTo ErrorHandler

Printit:

Dim SheetList(1 To 6) As String
Dim sCtr As Long

sCtr = 0
If PhaseCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "131 Phase1"
sCtr = sCtr + 1
SheetList(sCtr) = "131 Phase2"
End If

If ShopCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "131 Shop1"
sCtr = sCtr + 1
SheetList(sCtr) = "131 Shop2"
End If

If RFCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "RF"
End If

If StatusCheckBox.Value = True Then
sCtr = sCtr + 1
SheetList(sCtr) = "Status"
End If

If sCtr = 0 Then
'all the checkboxes were false
MsgBox "No Pages Checked", , "Error"
Else
'get rid of any unused elements in the array
ReDim Preserve SheetList(1 To sCtr)
Sheets(SheetList).PrintOut Copies:=1, Collate:=True
End If


Exit Sub

ErrorHandler:
Application.ScreenUpdating = True
End Sub
 
This seems to work good but you are right I do have the current active sheet
that I don't want to print. I don't know of anyway arround this. Since I
don't know witch sheets will be selected to print I don't know how i could
remove the FALSE from the first one.
 
Leave the false off of the first select and it will not include the active
sheet.

If PhaseCheckBox.Value = True Then Sheets("131 Phase1").select
If PhaseCheckBox.Value = True Then sheets( "131 Phase2").select false
If PhaseCheckBox.Value = True Then sheets( "131 Shop1").select false
.......
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


This seems to work good but you are right I do have the current active sheet
that I don't want to print. I don't know of anyway arround this. Since I
don't know witch sheets will be selected to print I don't know how i could
remove the FALSE from the first one.
 
I think you'll need something more like this:

Dim FirstSheet as boolean
FirstSheet = true

If PhaseCheckBox.Value = True Then
sheets("131 Phase1").select firstsheet
firstsheet = false
sheets("131 Phase1").select firstsheet
end if

if shopcheckbox.value = true then
sheets("131 shop1").select firstsheet
firstsheet = false
sheets("131 shop2").select firstsheet
end if

if rfcheckbox.value = true then
sheets("Rf Sheet").select firstsheet
firstsheet = false
end if

if statuscheckbox.value = true then
sheets("Status").select firstsheet
firstsheet = false
end if

if firstsheet = true then
'never changed to false, so no sheets were chosen
msgbox "No sheets selected"
else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
end if





If RFCheckBox.Value = True Then RF = "RF Sheet" Else RF = ""
If StatusCheckBox.Value = True Then Status = "STATUS" Else Status ""
 
Yes, you are right. My suggestion would only work if the first checkbox was
selected each time, which is not realistic.

--

Damon Longworth

2007 Excel / Access User Conference
London, England - Currently rescheduled
St. Louis, Missouri - Oct 24-26, 2007
www.ExcelUserConference.com/


I think you'll need something more like this:

Dim FirstSheet as boolean
FirstSheet = true

If PhaseCheckBox.Value = True Then
sheets("131 Phase1").select firstsheet
firstsheet = false
sheets("131 Phase1").select firstsheet
end if

if shopcheckbox.value = true then
sheets("131 shop1").select firstsheet
firstsheet = false
sheets("131 shop2").select firstsheet
end if

if rfcheckbox.value = true then
sheets("Rf Sheet").select firstsheet
firstsheet = false
end if

if statuscheckbox.value = true then
sheets("Status").select firstsheet
firstsheet = false
end if

if firstsheet = true then
'never changed to false, so no sheets were chosen
msgbox "No sheets selected"
else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
end if





If RFCheckBox.Value = True Then RF = "RF Sheet" Else RF = ""
If StatusCheckBox.Value = True Then Status = "STATUS" Else Status ""
 
Back
Top