Public Sub CopieListeEleve()
If InputBox("Saisissez le mot de passe en minuscule") <> "escalade" Then
MsgBox ("Mot de passe erroné")
Exit Sub
End If
' On déprotège toutes les feuilles
Déprotéger
Const I_LIG_DEB As Integer = 5
Const I_LIG_MAX As Integer = 23
Dim oShList As Worksheet
Dim oShAcc As Worksheet
Dim iDerLig As Integer
Dim iLig As Integer
Dim iEcrCol As Integer
Dim iEcrLig As Integer
Dim TabEleves() As Variant
Set oShList = Worksheets("Liste_élève")
Set oShAcc = Worksheets("Accueil")
'RAZ
oShAcc.Range("B" & I_LIG_DEB & ":H" & I_LIG_MAX).ClearContents
'écriture
iEcrCol = 2
iEcrLig = I_LIG_DEB
iDerLig = oShList.Range("A" & Rows.Count).End(xlUp).Row
TabEleves = Range("Tab_Eleves").Value
Application.ScreenUpdating = False
For iLig = LBound(TabEleves, 1) To UBound(TabEleves, 1)
If TabEleves(iLig, 1) <> "" Then
If iEcrLig > I_LIG_MAX Then
iEcrCol = iEcrCol + 2
iEcrLig = I_LIG_DEB
End If
'lien hypertexte
oShAcc.Hyperlinks.Add _
Anchor:=oShAcc.Cells(iEcrLig, iEcrCol), _
Address:="", _
SubAddress:="élève" & iLig & "!A2", _
TextToDisplay:=TabEleves(iLig, 1)
Worksheets("élève" & iLig).Unprotect
Worksheets("élève" & iLig).Range("A2").Value = TabEleves(iLig, 1)
Worksheets("élève" & iLig).Protect
'V0.2-fin
iEcrLig = iEcrLig + 2
End If
Next iLig
Application.ScreenUpdating = True
Set oShList = Nothing
Set oShAcc = Nothing
' On protège toutes les feuilles
Protéger
End Sub