VBA/Macro to delete particular sheets based on condition

[bakarken]

Hi all

I am preparing a spreadsheet which will have several sheets and a 'master' sheet. The user will first look at the master sheet and enter 'YES' or 'NO' as text in certain cells. I would like a macro where if the user types 'YES', CERTAIN sheets will be deleted.

For example;

If cell E30 (in 'MASTER' sheet) = 'YES', sheets 'Sheet A' and 'Sheet C' will automatically be deleted.
or if cell E40 (in 'MASTER' sheet) = 'YES', sheets 'Sheet B' and 'Sheet D' will automatically be deleted.

I am useless with macros, so if anyone with a response could tell me in WHAT sheet to paste the code and how to activate this, that would be great.

Much appreciated.

 

[gallen]

Hello and welcome.

This code will do what you ask. It needs to be in the code window of the 'Master' worksheet (Right click the 'Master' worksheet tab name and select 'ViewCode')

Paste this in the window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$E$30" 'Is the cell that has changed cell E30?
            'If the uppercase value of cell E30 is YES thendelete
            If UCase(Range("E30").Value) = "YES" Then
                If MsgBox("Delete Sheet A and Sheet C?", vbQuestion + vbYesNo, "Delete Sheets?") = vbYes Then
                    Sheets("Sheet A").Delete
                    Sheets("Sheet C").Delete
                End If
            End If
        Case "$E$40" 'Is the cell that has changed cell E40?
            'If the uppercase value of cell E40 is YES thendelete
            If UCase(Range("E40").Value) = "YES" Then
                If MsgBox("Delete Sheet B and Sheet D?", vbQuestion + vbYesNo, "Delete Sheets?") = vbYes Then
                    Sheets("Sheet B").Delete
                    Sheets("Sheet D").Delete
                End If
            End If
    End Select
End Sub

This checks for which cell has been changed. If it is the ones you have mentioned it will delete the sheets you requested.

****WARNING****, try this on a COPY of data. Deleting of sheets can't be undone.

 

[bakarken]

You, my friend, are my absolute hero. I shall name my first son after you, 'Gallen;.

One more question, is there a way that the same VBA can be applied, but once 'Yes' is entered, the spreadsheet doesn't ask 'Are you sure you want to delete Sheet A?', 'Are you sure you want to delete Sheet C?'?

In my question, I only gave examples but in the real sheet I would use this macro to delete 20+ sheets at any time, so it would be annoying to click 'Delete' 20 times.

Thank you.

 

[My Aswer Is This]

This script would be hard to use if your attempting to delete 20 sheets or so at a time. I wrote a similar script for you but did not post it when I saw gallen provided one that works
If you wanted to delete 20 or so you need to put all the sheet names you want deleted in a range and the script could step through the range deleting the sheets. For example enter all the sheet names in Sheet("Delete") Column "A"

This way you could modify the sheet names when you want. Hardcoding 20 sheet names in the code would be a hard job and would need to be changed every time.

I could write you a script to put all sheet names in a Range and then you could delete ones you do not want deleted. Or another option would be to put the value "Delete" into Range ("A1") on every sheet in the workbook you want deleted. And then run the script.

We can eliminate the "Are you sure" prompts by adding a line of code like:

Application.DisplayAlerts = False

 

[bakarken]

I am happy to use either method, entering the individual sheet names into column F of the 'MASTER' sheet would not be a problem because I could just hide the cells.

Also, I was also planning to include a tickbox (from the developer tab) that when ticked, the word 'Yes' would automatically appear via a formula in the cells, which would activate the macro.

In Gallen's example, the tickbox did cause the 'Yes' to appear in the relevant cells but did not prompt any sheets to be deleted, but the macro DID work when it was typed manually.

Is it possible to have a macro that:
- Deletes 20+ sheets
- Does not cause several alerts to appear
- Can be run by using a developer tickbox

?

 

[gallen]

You, my friend, are my absolute hero. I shall name my first son after you, 'Gallen;.

One more question, is there a way that the same VBA can be applied, but once 'Yes' is entered, the spreadsheet doesn't ask 'Are you sure you want to delete Sheet A?', 'Are you sure you want to delete Sheet C?'?

In my question, I only gave examples but in the real sheet I would use this macro to delete 20+ sheets at any time, so it would be annoying to click 'Delete' 20 times.

Thank you.

My Aswer Is This gives sound advice.

Deleting 20 + sheets at anyone time would be laborious to code to say the least.

I would definitely advicse against deleting without ANY messages. This code will delete however many sheets you require with only one confirmation:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandle
    Select Case Target.Address
        Case "$E$30" 'Is the cell that has changed cell E30?
            'If the uppercase value of cell E30 is YES thendelete
            If UCase(Range("E30").Value) = "YES" Then
                If MsgBox("Delete associated sheets?", vbQuestion + vbYesNo, "Delete Sheets?") = vbYes Then
                    Application.DisplayAlerts = False
                    Sheets("Sheet A").Delete
                    Sheets("Sheet C").Delete
                    Application.DisplayAlerts = True
                End If
            End If
        Case "$E$40" 'Is the cell that has changed cell E40?
            'If the uppercase value of cell E40 is YES thendelete
            If UCase(Range("E40").Value) = "YES" Then
                If MsgBox("Delete associated sheets?", vbQuestion + vbYesNo, "Delete Sheets?") = vbYes Then
                    Application.DisplayAlerts = False
                    Sheets("Sheet B").Delete
                    Sheets("Sheet D").Delete
                    Application.DisplayAlerts = True
                End If
            End If
    End Select
Exit Sub
errHandle:
    Application.DisplayAlerts = True
    MsgBox Err.Description, vbInformation, "Err Number: " & Err.Number
End Sub

 

[My Aswer Is This]

The first question would be:
Are the same 20 sheet names we want to delete always going to be the same?
A formula which causes a cell value to change cannot cause a script to automatically run.
Is there some standard thing that happens which is the reason a sheet needs to be deleted?

Like for example if Range ("A1") value is less then 20 delete this sheet?

 

[gallen]

I am happy to use either method, entering the individual sheet names into column F of the 'MASTER' sheet would not be a problem because I could just hide the cells.

Also, I was also planning to include a tickbox (from the developer tab) that when ticked, the word 'Yes' would automatically appear via a formula in the cells, which would activate the macro.

In Gallen's example, the tickbox did cause the 'Yes' to appear in the relevant cells but did not prompt any sheets to be deleted, but the macro DID work when it was typed manually.

Is it possible to have a macro that:
- Deletes 20+ sheets
- Does not cause several alerts to appear
- Can be run by using a developer tickbox

?

The checkboxes don't cause a WOrksheet_Change event to trigger. However you there are workarounds. I'd use an Active X checkbox so you can handle the click event. Also the linked cells change to TRUE or FALSE not Yes or No

 

[bakarken]

The sheet names will always remain the same.

Okay, instead of using a formula, how about I use the tickbox (and it's 'Cell Link' function) which will always chuck the words 'TRUE' or 'FALSE' depending on whether it is checked/unchecked? Instead of using the word 'Yes' to prompt the VBA, how about 'if the cell = TRUE, then run'? That would not technically be caused by a formula, but instead by this tickbox cell link (in format control). Would this work?

Not a standard thing, the user has to make a decision about the work I do and so they would answer certain questionnaire style questions in the 'MASTER' page that would delete the sheets. In other words it is the user who decides which sheets to keep or delete, not the spreadsheet data.

I hope this makes sense, thanks both.

 

[gallen]

There's a workaround if you wish to keep Yes/No

Assign this Macro to checkbox that changes Cell E30. This in turn will trigger the worksheet changed sub. I've just tested it and it works fine.

Sub CheckBoxE30_Click()
    
    If Range("E30") = True Then Range("E30") = "YES"
    If Range("E30") = False Then Range("E30") = "NO"
    
End Sub