Cannot delete worksheet 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!

[Jacob Hilderbrand]

Is the workbook protected? Can you attach the workbook?

[vonpookie]

Cross-posted:
http://www.mrexcel.com/board2/viewtopic.php?t=170819

[malik641]

Thanks DRJ, that was the problem
I would post the workbook, but I think that my job wouldn't like that (because of the data that is in it). And it would take TOO much time to make a dummie workbook. But again, thanks!

Here's what I ended up with (in full):

Private Sub OKButton_Click()
'On Error Resume Next
ActiveWorkbook.Unprotect Password:=WBPassword
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)
'Checks for Valid Employee Name
If EmpCombo.Value = "" Then
MsgBox "You have entered no employee name." & vbCrLf & _
"Please enter an employee name.", vbOKOnly + vbCritical, _
"Missing Employee Name"
EmpCombo.SetFocus
Exit Sub
End If
For Each EmpName In Range("Employees")
If EmpCombo.Text = EmpName Then
i = i + 1
End If
Next EmpName
If i < 1 Then
MsgBox "You have not entered a valid employee name." & vbCrLf & _
"Please enter a valid employee name.", vbOKOnly + vbCritical, _
"Invalid Employee Name"
EmpCombo.Text = ""
EmpCombo.SetFocus
Exit Sub
End If
'If No option was selected
If OptionHide = False And OptionDelete = False Then
MsgBox "You have not selected an option." & _
vbCrLf & vbCrLf & _
"Please select an option to continue.", vbOKOnly + vbExclamation, _
"No Option Selected"
OptionHide.SetFocus
Exit Sub
End If
'Actions based on which option selected - Delete or Hide
If OptionDelete Then
If MsgBox("You are about to delete the sheet named " & _
EmpCombo.Text & "." & vbCrLf & _
"Any information on this sheet will be taken out of all" & _
vbCrLf & "calculated values in the workbook." & _
vbCrLf & vbCrLf & "Are you sure?", _
vbOKCancel + vbExclamation, _
EmpCombo.Text & " is about to be" & _
" deleted...") <> vbOK Then
Unload EmpUserForm
ActiveWorkbook.Protect Password:=WBPassword
Exit Sub
End If
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
If WS.Name = EmpCombo.Text Then
WS.Delete
shName = EmpCombo.Text
Call DeleteExcess_SheetName
Exit For
End If
Next WS
Application.DisplayAlerts = True
End If
If OptionHide Then
If MsgBox("The sheet " & EmpCombo.Text & _
" will now be hidden. There will be no effects" & vbCrLf & _
"to any values, and the sheet will no longer be seen by" & vbCrLf & _
"any users. Though the sheet will still remain in the workbook." & _
vbCrLf & vbCrLf & _
"Are you sure?", vbOKCancel + vbInformation, _
EmpCombo.Text & " will be hidden...") _
<> vbOK Then
Unload EmpUserForm
ActiveWorkbook.Protect Password:=WBPassword
Exit Sub
End If
'Hides the worksheet selected and places the name of the worksheet in the
' defined name "Hide_Sheets" list
With ActiveWorkbook
.Unprotect Password:=WBPassword
For Each WS In ThisWorkbook.Worksheets
If WS.Name = EmpCombo.Text Then
WS.Visible = xlSheetHidden
shName = WS.Name
Call DeleteExcess_SheetName
NextName.Value = shName
End If
Next WS
.Protect Password:=WBPassword
End With
Unload EmpUserForm
End If
Unload EmpUserForm
ActiveWorkbook.Protect Password:=WBPassword
Application.ScreenUpdating = True
End Sub

The bold items are what I added.


Any comments/suggestions???
How does this look (as far as efficiency goes)???

Again, thanks

[Jacob Hilderbrand]

Glad you got it working.

I would break up some of the code.

Dim Prompt          As String
Dim Title           As String
Dim Response        As Long
If OptionDelete Then
Prompt = "You are about to delete the sheet named " & _
                EmpCombo.Text & "." & vbNewLine & _
                "Any information on this sheet will be taken out of all" & _
                "calculated values in the workbook." & _
                vbNewLine & vbNewLine & "Are you sure?"
    Title = EmpCombo.Text & " is about to be deleted..."
    Response = MsgBox(Prompt, vbOKCancel + vbExclamation, Title)
    If Response = vbOK Then
    '...
    End If
 
End If

[malik641]

Glad you got it working.

I would break up some of the code.

Dim Prompt As String
Dim Title As String
Dim Response As Long
If OptionDelete Then
Prompt = "You are about to delete the sheet named " & _
EmpCombo.Text & "." & vbNewLine & _
"Any information on this sheet will be taken out of all" & _
"calculated values in the workbook." & _
vbNewLine & vbNewLine & "Are you sure?"
Title = EmpCombo.Text & " is about to be deleted..."
Response = MsgBox(Prompt, vbOKCancel + vbExclamation, Title)
If Response = vbOK Then
'...
End If
End If

Cool, thanks DRJ