Hello,
I have following code that will delete the sheet named kirok when the user selects the values in the drop down list from "yes" to "no". But the problem is its not protecting the workbook back post deletion. can you please suggest?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim worksh As Integer
Dim worksheetexists As Boolean
Dim str1 As String
ActiveWorkbook.Unprotect Password:="abc123"
If Target.Address = "$E$39" Then
Select Case UCase(Target)
Case Is = "YES": Shapes("Button 6").Visible = msoTrue
Case Is = "NO": Shapes("Button 6").Visible = msoFalse
worksh = Application.Sheets.Count
worksheetexists = False
For x = 1 To worksh
If Worksheets(x).Name = "kirok" Then
worksheetexists = True
If MsgBox("Keyword Sheet will be deleted, data will be lost", vbOKCancel) = vbOK Then
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("kirok").Delete
ActiveWorkbook.Protect Password:="abc123"
Application.DisplayAlerts = True
Else
Target.Value = "Yes"
Exit Sub
End If
Exit For
End If
Next x
End Select
End If
ActiveWorkbook.Protect Password:="abc123"
End Sub
Bookmarks