Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets based on checkboxes being unchecked
I have a slight situation that I don't know how to resolve. My IT dept created a userform that prints sheets based on checkboxes that are checked on the userform. The unchecked pages are saved with the checked one's when the file is saved. Is there a way to tell the form to print the checked box (captions) and to delete the unchecked boxs? The checkbox Captions are the names of the actual sheets on the spreadsheet.
For example: If Checkbox "Jeff" is checked, then it would print the sheet "Jeff" but if Checkbox "Mary" is unchecked, then it would delete sheet "Mary" so that it isnt saved. Any way to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets based on checkboxes being unchecked
Sorry. Here it is. Thanks for your help.
Dim ctrl As Control Dim chkbx As MSForms.CheckBox For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.CheckBox Then Set chkbx = ctrl If chkbx Then Worksheets(chkbx.Caption).PrintOut End If End If Next "Bernie Deitrick" wrote: Tip, It would be easiest if you posted the code that is executed when you press the commandbutton to do the actual printing. Go into the VBE and double click on that button within the design view to see the code and copy it to post here. HTH, Bernie MS Excel MVP "Tip Top" <Tip wrote in message ... I have a slight situation that I don't know how to resolve. My IT dept created a userform that prints sheets based on checkboxes that are checked on the userform. The unchecked pages are saved with the checked one's when the file is saved. Is there a way to tell the form to print the checked box (captions) and to delete the unchecked boxs? The checkbox Captions are the names of the actual sheets on the spreadsheet. For example: If Checkbox "Jeff" is checked, then it would print the sheet "Jeff" but if Checkbox "Mary" is unchecked, then it would delete sheet "Mary" so that it isnt saved. Any way to do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets based on checkboxes being unchecked
Tip,
Try this. Change If chkbx Then Worksheets(chkbx.Caption).PrintOut End If To If chkbx Then Worksheets(chkbx.Caption).PrintOut Else Application.DisplayAlerts = False Worksheets(chkbx.Caption).Delete Application.DisplayAlerts = True End If HTH, Bernie MS Excel MVP "Tip Top" wrote in message ... Sorry. Here it is. Thanks for your help. Dim ctrl As Control Dim chkbx As MSForms.CheckBox For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.CheckBox Then Set chkbx = ctrl If chkbx Then Worksheets(chkbx.Caption).PrintOut End If End If Next "Bernie Deitrick" wrote: Tip, It would be easiest if you posted the code that is executed when you press the commandbutton to do the actual printing. Go into the VBE and double click on that button within the design view to see the code and copy it to post here. HTH, Bernie MS Excel MVP "Tip Top" <Tip wrote in message ... I have a slight situation that I don't know how to resolve. My IT dept created a userform that prints sheets based on checkboxes that are checked on the userform. The unchecked pages are saved with the checked one's when the file is saved. Is there a way to tell the form to print the checked box (captions) and to delete the unchecked boxs? The checkbox Captions are the names of the actual sheets on the spreadsheet. For example: If Checkbox "Jeff" is checked, then it would print the sheet "Jeff" but if Checkbox "Mary" is unchecked, then it would delete sheet "Mary" so that it isnt saved. Any way to do this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets based on checkboxes being unchecked
It returns a Runtime Error 9. Subscript out of string. It looks like it is trying to delete all the sheets including the one's that have the checkboxes checked.
"Bernie Deitrick" wrote: Tip, Try this. Change If chkbx Then Worksheets(chkbx.Caption).PrintOut End If To If chkbx Then Worksheets(chkbx.Caption).PrintOut Else Application.DisplayAlerts = False Worksheets(chkbx.Caption).Delete Application.DisplayAlerts = True End If HTH, Bernie MS Excel MVP "Tip Top" wrote in message ... Sorry. Here it is. Thanks for your help. Dim ctrl As Control Dim chkbx As MSForms.CheckBox For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.CheckBox Then Set chkbx = ctrl If chkbx Then Worksheets(chkbx.Caption).PrintOut End If End If Next "Bernie Deitrick" wrote: Tip, It would be easiest if you posted the code that is executed when you press the commandbutton to do the actual printing. Go into the VBE and double click on that button within the design view to see the code and copy it to post here. HTH, Bernie MS Excel MVP "Tip Top" <Tip wrote in message ... I have a slight situation that I don't know how to resolve. My IT dept created a userform that prints sheets based on checkboxes that are checked on the userform. The unchecked pages are saved with the checked one's when the file is saved. Is there a way to tell the form to print the checked box (captions) and to delete the unchecked boxs? The checkbox Captions are the names of the actual sheets on the spreadsheet. For example: If Checkbox "Jeff" is checked, then it would print the sheet "Jeff" but if Checkbox "Mary" is unchecked, then it would delete sheet "Mary" so that it isnt saved. Any way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need code for hiding/unhiding sheets using checkboxes | Excel Worksheet Functions | |||
Return value(s) based on selected checkboxes | Excel Worksheet Functions | |||
Return value based on checkboxes selected -- IF() LOOKUP() | Excel Worksheet Functions | |||
Return value based on checkboxes selected -- IF() LOOKUP() | Excel Worksheet Functions | |||
Sum Based on Checkboxes | Excel Discussion (Misc queries) |