CODEWITHSUNDEEP
excelvba
Manu Mohan
Manu Mohan

Reputation: 177

Allow Users to edit ranges in excel sheet in VBA

I have a work book which has different sheets having names as user names(windows login) of different users. Now I have added a code to add new sheet to the workbook for a new user. I wanted to give permission to the same user to edit certain ranges through the code in the new sheet created. If I unlock certain cells all the users will be able to edit the ranges in this user's sheet. How I can get the 'allow users to edit ranges' functionality through vba

    Sub add_new_user()

newUser = ActiveWorkbook.Sheets("Admin").newUserTextBox.Value

struserdn = GetUserFullName(newUser)

If struserdn <> "Error" Then

    answer = MsgBox("Do you want to add the following user?" & vbNewLine & vbNewLine & struserdn & vbNewLine & newUser & "@abcd.com", vbYesNo + vbQuestion, "New User")
    If answer = vbYes Then
        Dim newUSheet As Worksheet
        Worksheets("Example_sheet").Copy Before:=Worksheets("Example_sheet")
        Set newUSheet = ActiveSheet
        newUSheet.Name = newUser
        newUSheet.Unprotect "123" 

        'need some code here to allow the newUser to edit ranges A4:F10000

        newUSheet.protect "123"
        newUSheet.Rows("A1").Value = struserdn

    Else
        Exit Sub
    End If
Else
    MsgBox "User name not found!", , "Error"
    Exit Sub
End If

End Sub

Upvotes: 1

Views: 3950

Answers (2)

Jerry Lundgren
Jerry Lundgren

Reputation: 61

This piece of code loops through all sheets in the workbook and unlocks those sheets that has the same namne as the windows username.

edit: Added som lines to the code.

Dim winUser As String
Dim ws As Worksheet

winUser = Environ("username")

For Each ws In Worksheets

    If ws.Name = winUser Then

        ws.Unprotect     ' You can add a password here

    else

        ws.protect        ' You can add a password here

    End If

Next ws

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

With Range("A1:B1").Locked = False you can mark a range as unlocked in protection mode.

If you now use something like

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

to protect the sheet, then the previous unlocked range can be edited by a user and any other range of the sheet can not.

If you mean that user A can only edit sheet A and user B can only edit sheet B, this is not possible in any secure way.

Upvotes: 0

Related Questions