Need help to lock/unlock multiple sections of a worksheet as when clicked by user

[elfynstargazer]

Hi guys,

I am an excel savvy person, but not too expert like VBA or macro programmers. As such, I need some help on figuring out how to lock certain portions of my worksheet upon a click of a button.

Imagine a 30-day calendar spread page.
I need to have 30 buttons which will enable the user to lock only the columns under Day 1 when clicked on the button related to Day 1. And, similarly, to unlock it again upon clicking. The rest of the columns under Day 2 to Day 30 are still editable.

I have tried the basic 'protect page', 'allow users to edit ranges' method and a few other basic vba attempts which all didn't turn out to produce the results I want.

Pls pls, your help is much appreciated!!!

Thank you!!!

 

[HotRhodium]

What I do is add this to the top of one of my modules (before any sub or function)

Public Const MyPassword As String = "MyPassword"

Then I use something like

Dim MyStr As String
 MyStr = InputBox("Please input password", "Password")
 If MyStr = MyPassword Then
  ''Put code here
 End If

Code in Put code here will only execute if the password input is "MyPassword"

Does this help?

 

[elfynstargazer]

Hmm..it says that's an Invalid Outside Procedures on the red highlighted part of the code...

MyStr = InputBox("Please input password", "Password")

Also, you mean, to add an additional module? Because the code i gave initially was in 'This Workbook'.

 

[HotRhodium]

Yes your issue is that this should be in a standard module. don't worry about that now if you want it in ThisWorkbook just use this

Sub LockSelection()
 If Application.InputBox("Please input password", "Password", , , , , , 2) = "MyPassword" Then
  ''Put code here
 Else
  MsgBox ("Wrong Password")
 End If
End Sub

This should work anywhere in any object (Replace MyPassword with the password you want

 

[elfynstargazer]

Where do i fit in your code with my existing code?
Just add it on below like this ? I have a feeling it isn't so.

Sorry for the multiple questions...I'm not so good at it.

Private Sub Workbook_Open()
Dim wksht As Worksheet
For Each wksht In ThisWorkbook.Sheets
With wksht
.EnableOutlining = True
.Protect contents:=True, userInterfaceOnly:=True, AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End With
Next wksht
End Sub


Sub LockSelection()
If Application.InputBox("Please input password", "Password", , , , , , 2) = "MyPassword" Then
''Put code here
Else
MsgBox ("Wrong Password")
End If
End Sub

 

[HotRhodium]

Yeah this would be a really poor way to do this. This should be in a standard module. I rewrote this so it could work in an object and it will but this is a bad way to do this. Please add a module and put this code in it

 Public Sub LockSelection()
 Dim MyRng As Range
  If Application.InputBox("Please input password", "Password", , , , , , 2) = "MyPassword" Then
   Set MyRng = Selection
   MyRng.Locked = True
   MyRng.Interior.Pattern = xlGray8
  Else
   MsgBox ("Wrong Password")
  End If
 End Sub

Then add an active x control button to your sheet. Right click your active x control button and click view code. It should look like this

Private Sub CommandButton1_Click()

End Sub

In between these two lines of code should go

LockSelection eg

Private Sub CommandButton1_Click()
LockSelection
End Sub

This is a bit much but it is the correct way to do this.

Now you will have your excel objects (Button, Workbook and Worksheet) separate from your Sub. If this is too much all of this code can go in the button and will work but it can cause problems for you later. I hope that helps.

This is what it would look like in a button by itself

Private Sub CommandButton1_Click()
Dim MyRng As Range
 If Application.InputBox("Please input password", "Password", , , , , , 2) = "MyPassword" Then
  Set MyRng = Selection
  MyRng.Locked = True
  MyRng.Interior.Pattern = xlGray8
 Else
  MsgBox ("Wrong Password")
 End If
End Sub

 

[elfynstargazer]

Thank you so much!

This code prompts a password to be keyed in, even before locking it?
Also, instead of an active x button, can i just use a regular button and insert that vba code in? Because, i tried the active x button, it does not allow me to click it. =(

I thought of using the general 'protect sheet' under the 'Review' tab, but if i do that, i can't perform my grouping function anymore.

 

[elfynstargazer]

Oh wait, one more thing I think I should let you know as well.

My sheet is already bydefault, protected each time I open the file, but without a password...and the grouping still works.

So, i think, the problem is....although I create password protection, i can still do a 'master' unlock by unprotecting the sheet under Review...which does not require a password.