CODEWITHSUNDEEP
excelvba
KROLN
KROLN

Reputation: 300

Specifying Windows-username tu unprotect all sheet at once

I would like to write a simple macro to lift all sheet protection at once. It work's fine. But i would like to make 2 options of it.

1st to use inputbox to write password. Simple

2nd where I need your help, is to use Windows User names to define which are allowed to unprotect it without password (password is in code already defined).

How to use Environ.user to define which user can use that macro?

For example user: 1st "hackla" and 2nd "klaud"

My basic code looks so:

Sub TabelleEntsperren()
  Dim strPassw As String
  Dim wSheet As Worksheet

strPassw = "Athens"

 For Each wSheet In ActiveWorkbook.Worksheets
 wSheet.Unprotect Password:=strPassw
Next wSheet

End Sub

Upvotes: 0

Views: 142

Answers (2)

Guest
Guest

Reputation: 430

Option Explicit

'Private API declarations
#If VBA7 And Win64 Then
    Private Declare PtrSafe Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#Else
    Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBudffer As String, nSize As Long) As Long
#End If

'To get the computer name
Public Function getActiveComputerName() As String
Dim cn As String, ls As Long, res As Long

cn = String(1024, 0)
ls = 1024
res = GetComputerName(cn, ls)
If res <> 0 Then
    getActiveComputerName = Mid$(cn, 1, InStr(cn, Chr$(0)) - 1)
Else
    getActiveComputerName = ""
End If

End Function

'To get the identifier for the active user
Public Function getActiveUserName() As String
Dim cn As String, ls As Long, res As Long

cn = String(1024, 0)
ls = 1024
res = GetUserName(cn, ls)
If res <> 0 Then
    getActiveUserName = Mid$(cn, 1, InStr(cn, Chr$(0)) - 1)
Else
    getActiveUserName = ""
End If

End Function

Upvotes: 1

tsdn
tsdn

Reputation: 427

Do you mean something like this?

Sub TabelleEntsperren()
    Const strPassw As String = "yourPassword"
    Const usr1 As String = "hackla"
    Const usr2 As String = "klaud"

    Dim wSheet As Worksheet
    Dim isTrustedUser As Boolean
    Dim currentUsr As String

    currentUsr = Environ("username")
    isTrustedUser = currentUsr = usr1 Or currentUsr = usr2

    For Each wSheet In ActiveWorkbook.Worksheets
        If isTrustedUser Then wSheet.Unprotect Password:=strPassw
    Next wSheet

End Sub

Upvotes: 1

Related Questions