CODEWITHSUNDEEP
vbaexcel
ruedi
ruedi

Reputation: 5545

Give workbook write access only to certain users

I have a workbook with rawdata. Via a button a user can open a UserInterface and export different subsets of the data.

Now I am looking for a way that only allows certain users (depending on there windows username) to do changes to the rawdata but all users can still click the button to export data.

This is as far as I got yet:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub AAA()
Dim UName As String * 255
Dim L As Long: L = 255
Dim Res As Long
    Res = GetUserName(UName, L)
...
End Sub

The code give me the Windows Username of the person who is logged into the os. I guess I need to do something like (let's suppose HAL is a User who shall have access to the rawdata):

If UName = "HAL" then
---Unlock worksheet
End If

But I just cannot find how to set the workbook unlock and where (and when) to set the worksheet locked. I guess setting it locked during the Open Event is a bad idea. I really would appreciate if anyone could help me here.

Upvotes: 0

Views: 691

Answers (1)

Vincent G
Vincent G

Reputation: 3188

Something like that?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sh As Worksheet
    For Each sh In Worksheets
        sh.Protect "password"
    Next sh
    'or if only some Whorksheets need to be locked:
    'ThisWorkbook.Worksheets("RawDatas").Protect "password"
End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
    If UserIsAllowed() Then
        sh.Unprotect "password"
    End If
End Sub

Public Function UserIsAllowed() As Boolean
    UserIsAllowed = True
End Function

(you will need to change the UserIsAllowed function of course)

Upvotes: 1

Related Questions