PDA

View Full Version : Solved: Cannot delete worksheet from userform...Why?



malik641
09-27-2005, 11:56 AM
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??:dunno ??

Thanks in advance!

Jacob Hilderbrand
09-27-2005, 12:19 PM
Is the workbook protected? Can you attach the workbook?

vonpookie
09-27-2005, 01:16 PM
Cross-posted:
http://www.mrexcel.com/board2/viewtopic.php?t=170819

malik641
09-27-2005, 02:01 PM
Thanks DRJ, that was the problem :thumb
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 :thumb

Jacob Hilderbrand
09-27-2005, 02:32 PM
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
09-29-2005, 01:57 PM
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 :thumb