Create new worksheet by ticking checkbox and then delete worksheet by unchecking box

RoyUK,

That works great but what how would I need to amend the code if I wanted to generate a message box, if the box is then unchecked, asking the user whether they want the sheet deleted or not. If the user selects No, then the value of the checkbox needs to be set to True again.

Thanks

snb, your code works fine with the line below commented out, as this gives a Run-time error 1004 (Method 'Range' of object'_Worksheet' failed)

Please Login or Register  to view this content.

Also, if you uncheck the checkbox and click the No button on the message box, then you get another run-time 1004 error, because the code is trying to create another worksheet named "BROCS" but the original one is still there (because I selected not to delete).

Any other ideas?
Thanks

RoyUK,

The workbook has a top sheet which users will complete with various bits of infromation. The aim is to provide all of the relevant information that other users will require. However, occasionally the person completing the information may want to include a screen print from other systems that we have at work. To facilitate this, I have a series of check boxes named for the systems. If the person completing the spreadsheet checks a box, a worksheet will be created using a specific name (in this instance, BROCS). The user will then paste a screen print into the newly created worksheet. However, if the user then decides it is not required, I want them to be able to delete the worksheet by unchecking the box. I am also trying to account for someone incorrectly removing the check from the box, by asking whether they really want to delete the worksheet. If they don't then the code needs to repopulate the checkbox without this trying to create another worksheet with the same name.

Hope this clarifies what I am trying to do

OK, thanks for the advice Roy. I am attaching an example of what I am trying to achieve.

You will see that checking the box will generate another worksheet based on the template worksheet (which would be hidden in the real life version). The user may then decide that he doesn't need the worksheet created and will uncheck the box. This will produce a message box asking if the user wants to delete the sheet. If they do, the code works and the sheet is deleted. If they change their mind, I want the sheet to stay and for the box to remain checked - at the moment my code doesn't do this and I imagine I need something to check and see whether the sheet is already present?

Any suggestions?

Many thanks

Roy, thanks but, if the No option is selected on the message box, although the sheet is not deleted, neither is the checkbox value restored to TRUE - which is what I would like to happen to indicate that a worksheet with that name should be present

Is anyone able to give me an idea about the final piece of VBA that I need to recheck the box without the code looping back to the top and trying to create another worksheet with the same name?
TIA

RoyUk, yes I understand that and the code does work perfectly. Are you telling me that there is no way that I can reinstate the check box without triggering the code to create the worksheet again?

RoyUK

Yes, but if the user removes the check from the box and is presented with the message box and selects No (or Cancel) then the sheet remains in the workbook but the box is unchecked. If a user (and I am thinking about my lowest common denominator of user here) then checks the box again, an error will occur because the sheet is already present. I want to prevent that happening by making sure the checkbox value becomes TRUE again if the user selects to cancel the delete sheet action.

OK, I have been thinking and I wonder if I might be better implementing this via some sort of toggling button or something. Any views?

RoyUK, that works brilliantly. Many thanks for all of your time on this