|
CheckBox to select pages to print?
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) Darkstar wrote: 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 -- Dave Peterson |
CheckBox to select pages to print?
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/ "Darkstar" wrote in message ... 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 |
CheckBox to select pages to print?
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 |
CheckBox to select pages to print?
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. Darkstar wrote: 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 -- Dave Peterson |
CheckBox to select pages to print?
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 |
CheckBox to select pages to print?
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. "Damon Longworth" wrote in message ... 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/ "Darkstar" wrote in message ... 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 |
CheckBox to select pages to print?
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/ "Darkstar" wrote in message ... 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. "Damon Longworth" wrote in message ... 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/ "Darkstar" wrote in message ... 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 |
CheckBox to select pages to print?
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 "" Damon Longworth wrote: 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/ "Darkstar" wrote in message ... 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. "Damon Longworth" wrote in message ... 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/ "Darkstar" wrote in message ... 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 -- Dave Peterson |
CheckBox to select pages to print?
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/ "Dave Peterson" wrote in message ... 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 "" Damon Longworth wrote: 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/ "Darkstar" wrote in message ... 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. "Damon Longworth" wrote in message ... 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/ "Darkstar" wrote in message ... 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 -- Dave Peterson |
| All times are GMT +1. The time now is 08:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com