Comment affecter un username et password pour un classeur sur Excel
m
Bonjour;
chére amies j'ai un problème avec le VBA pour la conception d'un userfome afin d'affecter un nom d'utilisateur et un mot de passe pour mon classeur comportant 10 feuiles .SVP
Coordialement
s
Bonjour,
Possible via différentes approches, ci-après une proposition VBA avec une classe, je vous laisse adapter à votre userform.
Classe:
Option Explicit
Private Type TProtectSht
user As String
pwd As String
wkSheet As Worksheet
End Type
Private this As TProtectSht
Public Property Let user(ByVal user As String)
this.user = user
End Property
Public Property Get user() As String
user = this.user
End Property
Public Property Let pwd(ByVal pwd As String)
this.pwd = pwd
End Property
Public Property Get pwd() As String
pwd = this.pwd
End Property
Public Property Set wkSheet(ByVal wkSheet As Worksheet)
Set this.wkSheet = wkSheet
End Property
Public Property Get wkSheet() As Worksheet
Set wkSheet = this.wkSheet
End Property
Private Sub Class_Initialize()
this.user = vbNullString
this.pwd = vbNullString
Set this.wkSheet = ThisWorkbook.Worksheets.[_Default](1)
End Sub
Public Sub setValues(ByVal wkSht As Worksheet, ByVal user As String, ByVal pwd As String)
Set this.wkSheet = wkSht
this.user = user
this.pwd = pwd
End Sub
Implementation (proposition):
Option Explicit
Private lockedSheetList() As ProtectedWorksheet
' run first
Public Sub setUserAndPass()
ReDim Preserve lockedSheetList(1 To ThisWorkbook.Worksheets.Count)
Dim i As Long
For i = LBound(lockedSheetList) To UBound(lockedSheetList)
' set username and password
Dim inputs As Variant
inputs = getUserAndPwd(ThisWorkbook.Worksheets(i))
Set lockedSheetList(i) = New ProtectedWorksheet
lockedSheetList(i).setValues ThisWorkbook.Worksheets(i), inputs(0), inputs(1)
Next i
End Sub
' run second
Public Sub lockAllSheets()
Dim lockedSheet As Variant
For Each lockedSheet In lockedSheetList
lockedSheet.wkSheet.Protect Password:=lockedSheet.pwd
Next lockedSheet
End Sub
' run third
Public Sub unlockAllSheets()
Dim lockedSheet As Variant
For Each lockedSheet In lockedSheetList
unlockSheet lockedSheet
Next lockedSheet
End Sub
Public Sub unlockSheet(ByVal sheet As ProtectedWorksheet)
Dim inputs As Variant
inputs = getUserAndPwd(sheet.wkSheet)
If inputs(0) = sheet.user And inputs(1) = sheet.pwd Then
On Error GoTo notUnlock
sheet.wkSheet.Unprotect Password:=sheet.pwd
Else
MsgBox "Identifiants incorrects"
End If
notUnlock:
On Error GoTo 0
End Sub
Public Function getUserAndPwd(ByVal wkSheet As Worksheet) As String()
Dim vals(0 To 1) As String
' ask user
vals(0) = InputBox("Entrez un nom d'utilisateur pour la feuille :" _
& wkSheet.Name, _
"Entr�e des donn�es", vbNullString)
' ask password
vals(1) = InputBox("Entrez un mot de passe pour la feuille :" _
& wkSheet.Name, _
"Entr�e des donn�es", vbNullString)
getUserAndPwd = vals
End Function
Edit: j'ai retiré Module1. ... mauvais c/c