The code below allows the user to select a worksheet name listed in a drop down box and delete the worksheet.
It also allows the row, within a separate work sheet that contains the worksheet name reference list, to be deleted so it does not display in the drop down box for future selection and return an error.
During process, display of the confirmation box to "Delete" the selected worksheet or "Cancel" the operation appears. (Just as desired)
As long as "Delete" is selected in the confirmation box the macro works fine.
Should "Cancel" be selected in the confirmation box an error occurs providing "AutoFilter method of Range class failed".
Disabling the application.display prevents the error but does not provide the user the option to cancel.
I have unsuccesfully attempted to divide the code into sections, disable the application.display and create a userform with command buttons to redirect the flow to the desired Sub Command.
May there be a recommendation available to recognize the "Cancel" selection in the confirmation box and redirect the flow?
Thank you for your assistance.
Rlb53
.......
[vba]
Function SheetExists(strWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(strWSName)
If Not ws Is Nothing Then SheetExists = True
End Function
Private Sub fpdeletelist_Change()
Call findws
End Sub
Sub findws()
Dim strWSName As String
strWSName = fpdeletelist.Value
If strWSName = vbNullString Then
MsgBox "Operation Cancelled"
Exit Sub
End If
If SheetExists(strWSName) Then
Worksheets(strWSName).Activate
ActiveWorkbook.Unprotect
ActiveSheet.Delete
Sheets("data sheet").Select
ActiveSheet.Unprotect
With ActiveSheet
.AutoFilterMode = False
With Range("f1", Range("f" & Rows.Count).End(xlUp))
.AutoFilter 1, fpdeletelist.Value
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
ActiveSheet.Protect
ActiveWorkbook.Protect
Unload Deleteform2
Unload Deleteform1
Else
MsgBox "That sheet name does not exist!"
End If
Sheets("main controls").Select
End Sub
[/vba]