Solved: "Cancel" selection causes a break in flow

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]

Have you tried setting Application.DisplayAlerts to False and then back to True?

Yes.

"Application.DisplayAlerts = False" allows the flow to continue with desired resluts, however it does not provide the user a secondary means to confirm / cancel the information deletion process.

(Just in case they selected the incorrect worksheet name)

You could write your own confirmation box and branch like this

Code:

---

For Loop = start To stop
    Rem do stuff

    If MsgBox("Are you sure?", vbYesNo) = vbYes Then
        Application.DisplayAlerts = False
        Rem code to delete stuff
        Application.DisplayAlerts = True
    End If

Next Loop

---

Thank you....

I'll plug that in and respond.

Thank You Mike !

The process did not specifically require a Loop, but the installation of the Msgbox w/ YesNo controls along with corresponding If, Then, Else conditions.

I have been fighting this configuration for what seems "Forever", I knew it was going to be something reasonably simple, but I did not know how to acquire and process the appropriate response from the user to continue the flow correctly.

Thank you immensely for sharing your knowledge !

Below is the ending code that works like a Charm !

[vba]
Public Sub findws()
Dim strWSName As String

strWSName = fpdeletelist.Value
If strWSName = vbNullString Then
MsgBox "Valid Batch Number was Not Selected"
Unload Deleteform2
Sheets("main controls").Select
Exit Sub
End If

If SheetExists(strWSName) Then
Worksheets(strWSName).Activate


If MsgBox("Are you sure?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False


ActiveWorkbook.Unprotect
ActiveSheet.Delete

Application.DisplayAlerts = True

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 "Deletion Has Been Cancelled"
Unload Deleteform2
Unload Deleteform1

End If

Sheets("main controls").Select
Application.DisplayAlerts = True
End If
End Sub
[/vba]