Unprotecting sheet after running a macro

Hi Guys,

I have this macro msgbox running in my protected sheet, by the time I select "Cancel" on the message box, my sheet become unprotected.

I'd been done a few test, and my sheet just be unprotected just when I select "Cancel" on the macro message box.

Could you help me to fix this problem?

This is my macro:

	


















	
Sub AddDeleteRowStaff()
   ActiveSheet.Unprotect Password:="150480"

Dim MyInput As Integer
MyInput = Application.InputBox(Prompt:="Would you like to Add or Delete last row? " & vbNewLine _
                                       & "Enter 1 to Add row" & vbNewLine & "Enter 2 to Delete last row", _
                               Title:="Add or Delete last row", _
                               Default:=1, _
                               Type:=1)
If MyInput = 0 Then Exit Sub
With ActiveSheet.ListObjects(1)
    Select Case MyInput
        Case 1
            MsgBox "Adding row"
            Range("A8").End(xlDown).ListObject.ListRows.Add AlwaysInsert:=False
            '.ListRows.Add
        Case 2
            MsgBox "Deleting last row"
            Range("A8").End(xlDown).ListObject.ListRows(.ListRows.Count).Delete
            '.ListRows(.ListRows.Count).Delete
    End Select
End With

    
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="150480"

End Sub


 

Hi, Michell Feitosa,

please use Code-Tags for displaying your code.

Move the line to unprotect after the checking of the input like this:

Sub AddDeleteRowStaff()

Dim MyInput As Integer
MyInput = Application.InputBox(Prompt:="Would you like to Add or Delete last row? " & vbNewLine _
  & "Enter 1 to Add row" & vbNewLine & "Enter 2 to Delete last row", _
  Title:="Add or Delete last row", _
  Default:=1, _
  Type:=1)
If MyInput = 0 Then Exit Sub
ActiveSheet.Unprotect Password:="150480"
With ActiveSheet.ListObjects(1)
  Select Case MyInput
    Case 1
      MsgBox "Adding row"
      Range("A8").End(xlDown).ListObject.ListRows.Add AlwaysInsert:=False
      '.ListRows.Add
    Case 2
      MsgBox "Deleting last row"
      Range("A8").End(xlDown).ListObject.ListRows(.ListRows.Count).Delete
      '.ListRows(.ListRows.Count).Delete
  End Select
End With


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="150480"

End Sub

Ciao,
Holger