Worksheet will delete from userform...Why?

[malik641]

I keep getting runtime errors while trying to delete a worksheet that was specified by the userform. Here is the code:

Private Sub OKButton_Click() 
     'On Error Resume Next
    Application.ScreenUpdating = False 
    Dim WS          As Worksheet 
    Dim EmpName     As Variant 
    Dim cell        As Variant 
    Dim NextName    As Range 
    Dim i           As Long 
    i = 0 
    Set NextName = Range("Hide_Sheets").End(xlDown).Offset(1, 0) 
     
     'Actions based on which option selected - Delete or Hide
     '**************************************************************************  *********
    If OptionDelete Then 
        Application.DisplayAlerts = False 
        For Each WS In ThisWorkbook.Worksheets 
            If WS.Name = EmpCombo.Value Then 
                shName = WS.Name 
                Worksheets(shName).Delete 'Delete Method of Worksheet Class Failed
                Worksheets(WS.Name).Delete 'Delete Method of Worksheet Class Failed
                WS.Delete 'Method Delete of object '_Worksheet' failed
                 
                 'Goes to DeleteExcess_SheetName function to take out the name of the worksheet
                 '  from the Employees list
                Call DeleteExcess_SheetName 
                Unload EmpUserForm 
                Exit Sub 
            End If 
        Next WS 
         
        Application.DisplayAlerts = True 
    End If 
     '**************************************************************************  *********
    Unload EmpUserForm 
    Application.ScreenUpdating = True
End Sub

I shortened the code for relevancy, but if you need me to post the whole code for the OK button, let me know and I'll do so.

Under the OptionDelete, I've placed a couple of tries I had at deleting the worksheet. I placed the error I get right after them. I've tried these methods with a blank workbook and they worked fine, so what gives?? ??

Thanks in advance!

 

[Norie]

What errors are you getting?

 

[malik641]

The two "Delete Method of Worksheet Class Failed" and the one "Method Delete of object '_Worksheet' failed" errors. I placed them to the right of the posted code of where I tried to delete the sheets.

 

[Smitty]

How about just:

        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name = EmpCombo.Value Then ws.Delete
        Next ws

HTH,

Smitty

 

[malik641]

I got it. The workbook was protected and I forgot to unprotect it.

As for pennysaver's submission....I don't know why but that method wasn't working for me (I guess because of other dependent code)...but it's working fine the way I have it now. Thanks again!!