Deleting Sheets based on checkboxes being unchecked

[Guest]

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?

 

[Bernie Deitrick]

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

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.

 

[Guest]

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]

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

 

[Guest]

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.