Protecting a worksheet in multiple steps? Code

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
76
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:

Code:
[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-programming-vba-macros/1271564-protecting-a-worksheet-in-multiple-steps-code-example.html#post5098176
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
The password of the user, the administrator and the sheet must be different. That way you will avoid confusion.
In the events of thisworkbook

Code:
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:
Code:
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

Board Regular
Joined
Jan 29, 2019
Messages
76
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
AllowInsertingRows:=True,

What's up with the buttons?
 

Lil2606

Board Regular
Joined
Jan 29, 2019
Messages
76
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,081,575
Messages
5,359,723
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top