Delete Worksheet If Checkbox False

**heliskier89** · 06-16-2011, 04:53 PM

Can anyone give me some ideas on how to achieve the following?

If a checkbox is not selected (FALSE), then delete the worksheet(s) assosciated with the checkboxes and save the remaining workbook as a different file?

I've included a sample workbook but unfortunately no coding as I'd probably confuse matters.
In this example sheet 2 should be the only one saved.

Any tips or ideas gratefully received.

**Leith Ross** · 06-17-2011, 12:14 AM

Hello heliskier89,

Copy this macro into either the ThisWorkbook module or add a new module to project and save it there. Assign this macro to each Check Box. Be sure the text in the Check Box matches the worksheet's name.

Please Login or Register  to view this content.

**heliskier89** · 06-17-2011, 02:15 PM

Thankyou, your code works exactly as you say.

Instead of deleting each sheet one at a time, which would be the case with your method, I'd like to be able to tick each sheet checkbox first and then after having selected all I require I would like to run the macro, perhaps by clicking a button.

I've tried to include your code in the following but it comes up error '400'.

Please Login or Register  to view this content.

Any ideas?

**Leith Ross** · 06-17-2011, 04:28 PM

Hello heliskier89,

To do this with a button, the macro must check the state of each Check Box. Here is the corrected macro. Assign this macro to the button and remove any macros from the check boxes.

Please Login or Register  to view this content.

**heliskier89** · 06-18-2011, 02:15 AM

Again your code works great but would like to know if it's possible to tweak the following.

Running your code means that a standard warning box displays for each box that's ticked asking if you're sure you want to delete the cells. Can the code be tweaked so that this warning only shows once regardless of how many boxes are ticked?

Be sure the text in the Check Box matches the worksheet's name.

Noted your comment but can't understand why no text spaces are allowed because when I've used these boxes before you could have spaces in between text and also have numerous spaces prior to the start of the text which didn't affect the function!
I've attached a file with your working code and on sheet1 I've included a box similar to what I'd like with the corressponding tab to help explain.

Appreciate your guidance thus far and hope you can help me further.

**Leith Ross** · 06-18-2011, 11:26 AM

Hello heliskier89,

I corrected the macro. Leading and trailing spaces will be ignored in the Check Box captions. However, embedded spaces must match with the worksheet name. The alert dialog has also been disabled. here is the corrected macro.

Please Login or Register  to view this content.

**heliskier89** · 06-19-2011, 03:23 PM

You're a star Leith Ross. I can't thank you enough.

**andnew131** · 07-03-2012, 07:59 AM

Hello

I'm trying to follow this and I've tried to apply the solution given to the OP in my own workbook but I just can't seem to do it. I'm not sure which elements on the page need to have (or do have) which bits assigned to them. Also, being inexperienced, I don't know which parts of the macros are the variables. Where do I reference my sheet name for example?

I included the OP's sheets 1-3 into my workbook and I did manage to delete sheet 2 and 3. But that's it! My renamed sheet 1 says that the subscript is out of range.

I'd be very grateful if somebody could walk me through it.

Many thanks, any help is much appreciated.

Kind Regards
Andrea

**arlu1201** · 07-03-2012, 08:13 AM

Andrea,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.