Button Controlled Macro to Delete Sheet If It Exists

sbox134 · Nov 5th 2018

Hello,

I've got a workbook that users use a form control button to create additional sheets. I would also like to create a button to delete sheets if needed. The code below works to delete the sheet, but if the user tries to delete a sheet that doesn't exist it generates an error. Any way to modify the code so if a user enters the name of sheet that doesn't exist the macro won't generate an error?

Code

Sub btnDeleteOrg()


Dim orgname As Variant


'Delete Tab
    orgname = InputBox("Enter tab name:", "Enter Tab Name to Delete")
    If orgname = "" Then Exit Sub
    Sheets(orgname).Activate
    ActiveWorkbook.Unprotect
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.Protect


End Sub

Display More

Thank you!!

**alansidman** · Nov 5th 2018

Try adding the lines I have highlighted.

Code

Sub btnDeleteOrg()  
Dim orgname As Variant  'Delete Tab    
[COLOR=#FF0000]On Error Resume Next      
Err.Clear[/COLOR]    
orgname = InputBox("Enter tab name:", "Enter Tab Name to Delete")    
If orgname = "" Then Exit Sub    
[COLOR=#FF0000]If Err.Number <> 0 Then Exit Sub[/COLOR]    
Sheets(orgname).Activate    
ActiveWorkbook.Unprotect    
ActiveWindow.SelectedSheets.Delete    
ActiveWorkbook.Protect  
End Sub

Display More

graha_karya · Nov 5th 2018

Sbox134 you can use evaluate formula.to check exist sheet

Code

sub aaaa()
dim orgname
orgname = application.inputBox("sheet name")
If orgname = "" then exit sub
    If Evaluate("isreff('" & trim$(orgname) & "'!A1)") then
         msgbox "Sheet exists",64
         sheets(trim$(orgname)).delete
    else
        exit sub
    end if
end sub

Display More

sbox134 · Nov 5th 2018

Hi Alan,

Thank you for the help and prompt reply. I've tried the code you suggested, but if I enter a sheet name that doesn't exist the macro still deletes the active sheet instead of doing nothing. Any other ideas?

Below is the code directly out of the module:

Code

Sub btnDeleteOrg()


Dim orgname As Variant
On Error Resume Next
Err.Clear
orgname = InputBox("Enter tab name:", "Enter Tab Name to Delete")
If orgname = "" Then Exit Sub
If Err.Number <> 0 Then Exit Sub
Sheets(orgname).Activate
ActiveWorkbook.Unprotect
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Protect


End Sub

Display More

sbox134 · Nov 6th 2018

Thank you both AlanSidman and graha_karya for the replies. I wasn't able to get the code graha_karya provided to work, but after a few modifications was able to make it fit my needs. The code that worked for me, in case anyone has a similar issue, is:

Code

Sub btnDeleteOrg()
Dim orgname As Variant
orgname = InputBox("Enter tab name:", "Enter Tab Name to Delete")
If orgname = "" Then Exit Sub
    If Evaluate("ISREF('" & orgname & "'!A1)") Then
        ActiveWorkbook.Unprotect
        Sheets(orgname).Delete
        ActiveWorkbook.Protect
    Else
        Exit Sub
    End If
End Sub

Display More

Button Controlled Macro to Delete Sheet If It Exists

Participate now!

Share

Users Viewing This Thread