VBA code to lock range of cells based on a cell value

[Jmoz092]

I'm having trouble locking a range of cells if a VBA password prompt does not enter specific user names into a cell. I want any users to be able to edit range A1:I11, but I only want 2 specific users to be able to edit anything else on the sheet (codename sheet1).

The user's name is determined at a password prompt and then entered in to cell K18. If the value of K18 does not equal "user1" or "user2", then I want to lock range A12:R61.

I can't get the code to keep the cells locked, and I think I'm also missing the worksheet_change function. Is there something similar for range_change??

I have this code inserted into Sheet1:

Sub worksheet_change(ByVal target As Range)


Dim pwR As Range
Set pwR = Range("K18")


If pwR <> "user1" Or pwR.Value <> "user2" Then

range("a1:I11").Locked = False
Range("a12:r61").Locked = True
MsgBox "You do not have authorization to edit these cells." & vbCr & "Only Zuul, user1, or user2 may edit these cells." & vbCr & "The range is now locked."
End If


End Sub

 

[mumps]

This is what I would suggest: Start by unlocking A1:I11 and also unlock cell K18. These cells will always remain unlocked for data entry. All the cells in the rest of the sheet should be locked. Protect the sheet with a password. Place the following macro in the worksheet code module. Change "myPassword" to the password you used to protect the sheet (3 occurrences). Enter "user1" or "user2" in K18 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K18")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="myPassword"
    If Target = "user1" Or Target = "user2" Then
        Range("A12:R61").Locked = False
        ActiveSheet.Protect Password:="myPassword"
    Else
        Target.ClearContents
        MsgBox "You do not have authorization to edit these cells." & vbCr & "Only Zuul, user1, or user2 may edit these cells." & vbCr & "The range is now locked."
        ActiveSheet.Protect Password:="myPassword"
    End If
End Sub

Keep in mind that A12:R61 will remain unlocked for the next user so you will have to unprotect the sheet to lock them again and then protect the sheet again.

 

[mumps]

Here is a modified version. It works the same way except that all you have to do to get the sheet ready for the next user is to delete the password entered by the current user in K18 which will re-lock A12:R61.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K18")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="myPassword"
    If Target = "user1" Or Target = "user2" Then
        Range("A12:R61").Locked = False
        ActiveSheet.Protect Password:="myPassword"
    ElseIf Target = "" Then
        Range("A12:R61").Locked = True
        Range("K18").Locked = False
        ActiveSheet.Protect Password:="myPassword"
    Else
        Target.ClearContents
        MsgBox "You do not have authorization to edit these cells." & vbCr & "Only Zuul, user1, or user2 may edit these cells." & vbCr & "The range is now locked."
        ActiveSheet.Protect Password:="myPassword"
    End If
End Sub

 

[Jmoz092]

the password input (that generates the user's name) is part of a workbook_open sub:

Private Sub workbook_open()


Dim pass As String
    pass = InputBox("Enter Form Password To Continue, All Changes Will Be Recorded")
        If pass = "aaa" Then
    Range("AA10").Value = "Zuul"
        ElseIf pass = "bbb" Then
    Range("AA10").Value = "user1"
        ElseIf pass = "ccc" Then
    Range("AA10").Value = "user2"
        ElseIf pass = "ddd" Then
    Range("AA10").Value = "user3"
        ElseIf pass = "eee" Then
    Range("AA10").Value = "user4"
        ElseIf pass = "fff" Then
    Range("AA10").Value = "user5"
        ElseIf pass = "ggg" Then
    Range("AA10").Value = "user6"
        ElseIf pass = "hhh" Then
    Range("AA10").Value = "user7"
        ElseIf pass = "iii" Then
    Range("AA10").Value = "user8"
    End If

There's a whole bunch of msgBox prompts after this that input data into some cells in A1:I11.

AA10 is outside the print range, so the user name is copied to cell k18, so that it can be seen if the excel file is printed...or more accurately, when the excel file is printed as a PDF later in this process's life.

Your method then, would require the user to input a different password? Or they would be entering multiple passwords?

 

[mumps]

Your Workbook_Open macro will accept any password entered by the user. There is no way of checking whether that password is valid or not. Also a user can enter a particular password the first time and then a different password each time after that and it will be accepted. Is this how you wanted the macro to work? Also, how is the password transferred to K18? Is it entered manually by the user or is it copied and pasted or is it done by a formula? Are the passwords such as "aaa", "bbb" assigned to the users by you or do they choose their own password. I'm not clear on how the whole process works.

 

[Jmoz092]

I have this after the code in post #4 :

If sheet1.Range("AA10").Value = 0 Then
MsgBox "A valid password was not entered; the workbook will now close"
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If

K18's value is set in a worksheet formula, simply "=AA10".

The password was initially created so that I could use it to identify the user accessing, changing, creating data in the workbook. I plan/planned to use it in conjunction with a change log (still struggling with that). The users aren't aware that the password popup box can be bypassed, they're more novice excel users than myself. Each user picks their own password and I write it in to the code.

I guess I could append the code to list all of the "valid" passwords and then ActiveWorkbook.Close if the password entered is not "valid". I probably will later, I just got back from vacation and am revisiting this project today, after user feedback.

 

[Jmoz092]

What if I just hide the area that I don't want people editing? How do I do that?

If the user is not user1 or user 2, how do I hide every column after and including O, and then hide every row after and including 12?

 

[Jmoz092]

Well, hiding the rows doesn't work. It messes up the placement of all the check boxes on the worksheet.

 

[mumps]

I would suggest that you place all the valid passwords in a separate sheet and then make that sheet xlVeryHidden. The Workbook_Open macro could prompt the user for their password, check in the hidden sheet to see if the entered password is in the list of valid passwords and if it is, give the user access to the file. Would this work for you?

 

[Jmoz092]

I would suggest that you place all the valid passwords in a separate sheet and then make that sheet xlVeryHidden. The Workbook_Open macro could prompt the user for their password, check in the hidden sheet to see if the entered password is in the list of valid passwords and if it is, give the user access to the file. Would this work for you?

That's a great idea on making a password array, I like that very much. It'll make future maintenance easier.

The problem here is that we want only zuul, user1, or user2 to be able to change anything I the range A12:R61. In this range is a lot of checkboxes that are connected to true/false boxes on Sheet1 in the range AC4:AO31, and 20 cells that contain validated lists, in drop down fashion, in range A12:H16.

But, before Zuul, user1, or user2 access the file to make any changes, there are many other users that can (and do) create the excel file. When these users do this, they put demographic information into some cells in the range A1:I11. We do not want these users to be allowed to enter data into, or change any information in the range A12:R61 though.

Does that make a little more sense?

I'd like to not use a second password to allow changes to the workbook/worksheet since the user has already entered a password at workbook open so that the workbook can log their name.