Unlocking Edit Ranges In Excel Using VBA

I am using VBA to add information to my spreadsheet whenever data is entered into a different column. However, to do this, I unlock and lock the sheet as part of the vba script. This requires the user to re input their password to edit the next line. Is there a workaround for this?

Jeff

Posted 2018-09-20T16:50:58.977

Reputation: 1

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

Answers

I did some quick research on this question, and found that one option, to which Thales alluded, would be to pass the password as an argument into your VBA.

'Protect worksheet with a password
Sheets("Sheet1").Protect Password:="myPassword"

'Unprotect a worksheet with a password
Sheets("Sheet1").Unprotect Password:="myPassword"

The downside of this is that your password would be visible to anyone who can open your code. So you might want to create a second password to protect your macro code if you go down that route. See the link below.

https://stackoverflow.com/questions/11981410/unprotect-sheet-with-password-without-exposing-password-in-the-macro

EDIT: Based on your comment above, it might help us to see your code, then we can figure out where it makes sense to handle the password.

user2800

Posted 2018-09-20T16:50:58.977

Reputation: 166

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.

Rajesh S

Posted 2018-09-20T16:50:58.977

Reputation: 6 800