Can a formula be made to delete worksheets

[Oneida1000]

I am working on an Excel workbook and I was wondering if a formula can be written that, if "X" was entered in a specific cell, selected worksheets from that workbook would be deleted.

We are trying to create templates for a specific product; that product can be installed with various sign elements. Our template workbook lists all sign element options. We want to be able to send this workbook to customers but, we also want to reduce the size of the file being sent via email and it would be beneficial if, once the specific sign option was identified in the specified cell all other sign option sheets would be deleted automatically.

 

[Smitty]

Formulas can't take physical actions, but you can use VBA code to respond to actions and formula calculations. In your case, I'd think that a Change event would work.

In this example the range for the X's is in A1:A10, and it assumes that the list of sheet names in B1:B10 (that's the Target.Offset part). It doesn't suppress the normal error message that you'll get when trying to delete a sheet, so you'll get a warning. Note that there's no error handling in case a sheet doesn't exist, or other factors, but it's a start:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1:A10")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Value = "X" <SPAN style="color:#00007F">Then</SPAN> Sheets(Target.Offset(, 1).Value).Delete<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Oneida1000]

Smitty, I think I am in over my head as your reply is a foreign language to me.........lol. I am an Excel novice and am not familiar with VBA code and how to manipulate it.

Is there an area I can go to where I can get a "Excel for Dummies" translation of your response?

Not trying to be a wise guy (I'm afraid I may be coming off like that); I just don't know how to do what you've suggested.

Thanks for your help

 

[Rick Rothstein]

The rule-of-thumb to keep in mind is that a formula can take no action other than to return a value to the cell they are in. So the answer to your question is, no, a formula cannot be written to do what you asked. However, there are ways to do it using VBA coding. I would want to know a couple of things before trying to write it for you though. First, what is the layout of the sheet where you put the X's (what column does the X go in, what column is the sheet name in, and so on)? Second, after the sheet is deleted, did you want the row the X is on to be deleted as well? Third, it is not completely clear to me whether the sheets with the X are being deleted or if the sheets without the X are being deleted... could you clarify that for me please? By the way, I am guessing that you will be doing these deletions on a copy of your "template" sheet and not to the master sheet itself, right?

 

[Rick Rothstein]

Smitty, I think I am in over my head as your reply is a foreign language to me.........lol. I am an Excel novice and am not familiar with VBA code and how to manipulate it.

@Oneida1000,

We can guide you through the process as it is quite easy, but I would like to see the answers to the questions I asked first.

@Smitty (and Oneida1000),

I don't think I would use the Change event to do this operation... accidentally typing an X in the wrong spot might produce undesireable effects if not caught timely.

 

[Smitty]

Smitty, I think I am in over my head as your reply is a foreign language to me.........lol. I am an Excel novice and am not familiar with VBA code and how to manipulate it.

Is there an area I can go to where I can get a "Excel for Dummies" translation of your response?

Right here.

As Rick mentioned, make sure that you test this (and any code) on a COPY of a workbook first. There is no Undo button for code once it's been run other than closing without saving (which can have its issues).

Right-click the worksheet tab and select View Code. The Visual Baisc Editor (VBE) will open, and you can paste the code in the new window on the right. ALT+Q will exit you back to Excel where you can test the code.

In this case, entering an X in A1:A10 will delete the sheet name that's in B1:B10. Remember what I said about no error handling, so if a sheet doesn't exist then the code is going to give you an error (error handling is usually added at the end once you've identified potential errors).

EDIT:

I don't think I would use the Change event to do this operation... accidentally typing an X in the wrong spot might produce undesireable effects if not caught timely.

Good point Rick. Give people an opportunity to screw something up and they will.

 

[Oneida1000]

The workbook I am working on has 5 tabs; tabs 2-4 are labeled with a part number which relates to a specific sign layout. The page associated with tab 1 has a cell (C8) where the User will be able to enter the part number of the sign layout they will be using.
I'd like the sheet to be able to eliminate all sign layout tabs other than the one identified in cell C8 on page 1 of the workbook.

The final outcome would be that page 1, the page containing cell C8 with the sign layout part number desired, would remain as well as the page associated with the desired sign layout and page 5 would remain. In the end I would have a workbook containing 3 pages (tabs) instead of the original 5.

 

[Rick Rothstein]

Okay. First, though, I just want to reiterate my understanding that you will always be doing the deletions on a copy of your master template, never the template workbook itself. If that is try, then give this a try...

Do the following to your master template workbook so that the code is always available in the copies you make from it. Press ALT+F11 to go into the VB editor and, once there, click Insert/Module from its menu bar. Next, copy/paste the following code into the code window that opened up...

Sub DeletePartNumberSheets()
  Dim X As Long, SheetIndex As Long
  On Error GoTo NoSuchSheet
  SheetIndex = Worksheets(Range("C8").Value).Index
  Application.DisplayAlerts = False
  For X = Sheets.Count - 1 To 2 Step -1
    If Worksheets(X).Name <> Range("C8").Value Then Worksheets(X).Delete
  Next
  Application.DisplayAlerts = True
  Exit Sub
NoSuchSheet:
  MsgBox "There is no sheet labeled " & Range("C8").Value & "!", vbCritical, "No Such Part Number"
End Sub

That's it, the macro is ready for use. To test that this works correctly, make a copy of the master template workbook and put a part number (which is a sheet name) into Cell C8 and then press ALT+F8 to bring up the macro dialog box. Next, select the name DeletePartNumberSheets from the list and click the Run button. Your workbook should now consist of three sheets... the first and last sheet plus the sheet corresponding to the part number you typed into Cell C8. This is the procedure you would follow for your actual data sheet.

One note though... because it is a macro, the code will remain in the copy data workbook. There is no way (that I am aware of) to get the code to delete itself once it is no longer needed. Since you are distributing the modified workbook to others, you may want to make it part of your ritual to press ALT+F11 and delete the code from the Module, then when you save your workbook, the code will be gone from the workbook you are distributing.

 

[Smitty]

There is no way (that I am aware of) to get the code to delete itself once it is no longer needed.

It may be a bit more than what's needed here, but Jacob Hildebrand has a method: http://www.vbaexpress.com/kb/getarticle.php?kb_id=93

 

[Oneida1000]

Wow, you guys are amazing!!!!!
I tried it in a test copy of the master file and it worked just like I had hoped it would; thank you very, very much.