Comment affecter un username et password pour un classeur sur Excel

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

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