I can suggest you two possible methods, will allow certain user to edit specified data range.
Method 1 (VBA Macro):
Private Sub Workbook_Open()
Dim winUser As String
winUser = Environ("username")
If winUser = "Admin" Then
Range("A1:I100").Select
ActiveSheet.Protection.AllowEditRanges.Add Title:="AdminRange", Range:=Range( _
"A1:I100"), Password:="123"
End If
If winUser = "User1" Then
Range("A200:I400").Select
ActiveSheet.Protection.AllowEditRanges.Add Title:="User1Range", Range:=Range( _
"A200:I400"), Password:="231"
End If
End Sub
Method 2:
- Hit Review Tab then Click Allow Users to Edit Ranges.
- Click New button then New Range dialog box appears.
- Write Range Name, Cell Reference (Range) & Password (Password is optional).
- Click Permissions, then Add to open Select Users or Groups
- If you can't find Name/Group then click the Advanced button.
- Click Find Now.
- Then click User or Group and finish with the OK button.
- After the User/Group name has been added you can define whether the password if required to edit the range.
- Finally finish with Ok.
Note, adjust cell references and alter User's name in the VBA code as needed.
I don't know exactly how your sheet is, but does it work if the cells the user should input data are unlocked (Cells -> Format -> Format Cells -> Protection)? Does the user input the password to lock or unlock the sheet? You can pass the password as an argument in the vba function, so the user shouldn't need to input it every time. – Thales – 2018-09-20T17:29:11.080
I do pass the password as an argument in the vba function to protect/unprotect the sheet. However, I also go to Review>Protect>Allow Edit Ranges and allow an edit range for a particular user. The reason I do this is I only want certain users to be able to edit certain cells. The problem is when the vba function runs, it enters the password to unlock the sheet, then enters the password again to lock the sheet and then the user has to enter their password again in order to edit the range that they are allowed to edit. – Jeff – 2018-09-20T18:47:30.063
@Jeff, why don't you edit the OP & include the VBA code you are using to Lock/Unlock the Sheet. And better edit the post & include that your idea is to allow certain user to edit specific Data Range !! – Rajesh S – 2018-09-21T09:11:12.217