Unlocking a range based on combobox value-CodePudding

I have a user form login, where the user has to input the name and password and select from a combobox his role. My sheet is password protected, and I want to allow the user with the “x” role selected in the combobox to be allowed to input data only in range C:D, and the user with the “y” function to be allowed to input data only in range E:F. How can I do that if someone can help me?

CodePudding user response：

This will give you direction:

With Sheets("Target")
    .Unprotect "Password"
    .Cells.Locked = True
    .[C:D].Locked = False
    .Protect "Password"
End With

CodePudding user response：

Here's a part of your solution. The role can be written in cell A1. This code example recognizes the roles "Manager" and "Worker". It unlocks columns C:D or E:F if any of these roles are written in cell A1. Otherwise all cells are locked

First you need to use an Change event on your worksheet. This is done by clicking on Sheet1 in your project tree in VBA editor. The code you write there is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 1 Then
        ' If cell "A1" (row 1, column 1) is changed, then call the unlock function...
        UnlockByRole
    End If
End Sub

In a VBA Module you enter the following code:

' This function is used to unlock cells via a range string, e.g. "C:D"
Sub UnlockColumns(ColumnRange As String)
    ActiveSheet.Unprotect Password:="mypassword"
    Cells.Locked = True
    Cells(1, 1).Locked = False ' Cell A1 is always editable in this example
    If ColumnRange <> "" Then
        Columns(ColumnRange).Locked = False
    End If
    ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sub UnlockByRole()
    If Cells(1, 1).Value = "Manager" Then
        UnlockColumns "C:D"
    ElseIf Cells(1, 1).Value = "Worker" Then
        UnlockColumns "E:F"
    Else
        UnlockColumns ""
    End If
End Sub

Explaination The first part of the code adds an event listener. It is called/triggered when you change something in your worksheet.

The second part is used to unlock/lock specific parts of the worksheet. The function UnlockColumns can be used to unlock any range of the worksheet. It also locks the rest of the worksheet (except "A1" in this case). The function UnlockByRole is simply used to detect which role you have entered in cell "A1" (i.e. "Manager" or "Worker".. all other strings are ignored and locks the entire workseheet)