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.

 

[Rick Rothstein]

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

@Smitty,

Hey thanks for that. I've not had occasion to "play" with VBProjects before, but the code at the link you posted worked great.

@Oneida1000,

The code at the link Smitty provided works great, so I have incorporated it into the code I posted earlier and reproduced it below. If you replace the code I gave you previously with this code, you won't have to worry about deleting the code on the copy you distribute. Remember though, put this code in your master template workbook but only run the code from copies of that master template workbook that you make.

Sub DeletePartNumberSheets()
  Dim X As Long, SheetIndex As Long
  On Error GoTo NoSuchSheet
  SheetIndex = Worksheets(Range("C8").Value).Index
  On Error GoTo 0
  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
  On Error Resume Next
  With ActiveWorkbook.VBProject
    For X = .VBComponents.Count To 1 Step -1
        .VBComponents.Remove .VBComponents(X)
    Next X
    For X = .VBComponents.Count To 1 Step -1
        .VBComponents(X).CodeModule.DeleteLines _
        1, .VBComponents(X).CodeModule.CountOfLines
    Next X
  End With
  On Error GoTo 0
  Exit Sub
NoSuchSheet:
  MsgBox "There is no sheet labeled " & Range("C8").Value & "!", vbCritical, "No Such Part Number"
End Sub