Protecting a worksheet in multiple steps? Code

[Lil2606]

Hi all,

I'm trying to set up protection on my excel sheet.. and the way I imagine it, would be, when someone opens the workbook, they have to input a password, if they don't or the password isn't correct, they can only observe the workbook, and can't even select cells or do anything. Then if the password is correct, the next .Protect gets applied. Which allows some things but not everything, and to get the Sheet completely unprotected, there would be a button which will ask for the Admin Password and that will unprotect it completely, so the structure is okay but.. its just not exactly right as I code it...

Can I get some help with this please?

Here is my code, and my exact question within:

[COLOR=#333333]Sub Protect_my_sheet()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
'Private Sub Auto_Open()

Dim Pass As Variant

Pass = InputBox("Password?")

ActiveSheet.Protect 123
Sheet1.EnableSelection = xlNoSelection

If Pass = 123 Then

Sheet1.Unprotect Pass
Sheet1.EnableSelection = xlNoRestrictions

'Why can't I add rows to a table? Also if I try adding more statements eg.:Userinterfaceonly: = True I get a syntax error??

Sheet1.Protect Password:="456", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=True, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=True, _
    AllowSorting:=True, _
    AllowFiltering:=True, _
    AllowUsingPivotTables:=False

ElseIf Pass <> 123 Then

MsgBox "Password is incorrect. You are only allowed to observe this Workbook."

End If
     </code>[COLOR=#333333]   End Sub[/COLOR]

Posted on Excelforum as well link: https://www.excelforum.com/excel-pr...-multiple-steps-code-example.html#post5098176

 

[DanteAmor]

The password of the user, the administrator and the sheet must be different. That way you will avoid confusion.
In the events of thisworkbook

Private Sub Workbook_Open()
   Dim pass As String
   pass = InputBox("Password?")
   If pass = "[B][COLOR=#ff0000]only[/COLOR][/B]" Then
      '[COLOR=#0000ff]allows some things[/COLOR]
      sheet1.Unprotect "[COLOR=#ff0000][B]master[/B][/COLOR]"
      sheet1.Protect Password:="[B]master[/B]", _
         DrawingObjects:=True, _
         Contents:=True, _
         Scenarios:=True, _
         AllowFormattingCells:=False, _
         AllowFormattingColumns:=False, _
         AllowFormattingRows:=False, _
         AllowInsertingColumns:=False, _
         AllowInsertingRows:=False, _
         AllowInsertingHyperlinks:=False, _
         AllowDeletingColumns:=False, _
         AllowDeletingRows:=False, _
         AllowSorting:=False, _
         AllowFiltering:=[B][COLOR=#0000ff]True[/COLOR][/B], _
         AllowUsingPivotTables:=[B][COLOR=#0000ff]True[/COLOR][/B]
      sheet1.EnableSelection = xlUnlockedCells
   Else
   'only view
      MsgBox "Password isn't correct, you can only observe"
      sheet1.Unprotect "[B]master[/B]"
      sheet1.Protect Password:="[B]master[/B]", _
         DrawingObjects:=True, _
         Contents:=True, _
         Scenarios:=True, _
         AllowFormattingCells:=False, _
         AllowFormattingColumns:=False, _
         AllowFormattingRows:=False, _
         AllowInsertingColumns:=False, _
         AllowInsertingRows:=False, _
         AllowInsertingHyperlinks:=False, _
         AllowDeletingColumns:=False, _
         AllowDeletingRows:=False, _
         AllowSorting:=False, _
         AllowFiltering:=[COLOR=#ff0000]False[/COLOR], _
         AllowUsingPivotTables:=[COLOR=#ff0000]False[/COLOR]
      sheet1.EnableSelection = xlNoSelection
   End If
End Sub

In a module:

Sub Button()
   Dim pass As String
   pass = InputBox("Admin Password?")
   If pass = "[COLOR=#ff0000][B]administrator[/B][/COLOR]" Then
      sheet1.Unprotect "[B]master[/B]"
   Else
      MsgBox "Password isn't correct, you can only observe"
   End If
End Sub

 

[Lil2606]

Thanks Dante, I think I got the idea..

I have tables on this sheet and I would like the User to be able to add rows to the tables but not delete them and columns can't be added or deleted.. and of course to use the buttons I wrote the macros for.. what do I need to make True or false for that?

 

[DanteAmor]

AllowInsertingRows:=True,

What's up with the buttons?

 

[Lil2606]

I have the AllowInsertingRows:=True in my code.. but I still wasn't able to insert rows into a table.. I could only insert rows into the sheet.. but I would like my user to be able to do it.

I'm thinking ahead... on my Sheet2 I have a a cell into which the user has to insert raw data and then they press a button and the raw data magically becomes a table with some columns deleted and duplicte rows removed and such, and another button to reset this and so my user needs to be able to paste data into this and use the buttons on this Sheet.. I have my "User interface" with instructions and the buttons in the first 5 rows so I persume only those need to be locked on this sheet, just when they are locked the user still needs to be able to use the macros.

 

[DanteAmor]

There is no way to insert rows in a table.

The user can capture data in a sheet2, and with a macro copy the data from sheet2, unprotect sheet1, paste new data in sheet1 and protect the sheet1 again.