1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
| Private Sub UserForm_activate()
Dim Auditeur, Audite, ValHab As Long
Call CommandButton3_Click
Me.ComboBox3.List = Application.Transpose(Sheets("parametres").Range("AG6:AO6"))
End Sub
Private Sub CommandButton2_Click() 'Bouton Quitter
Unload Me
End Sub
Private Sub CommandButton3_Click() 'Bouton Réinitialisation
Me.Height = 285
If CheckBox1.Value = True Then
ListBox1.RowSource = ""
Else
ListBox1.Clear
End If
For i = 1 To 3
Me.Controls("ComboBox" & i).Enabled = True
Me.Controls("combobox" & i).Value = ""
Next
CheckBox1.Value = False
ListBox1.Visible = False
End Sub
Private Sub CommandButton1_Click() 'Bouton Rechercher
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Me.Height = 400
'-------------------------------------------------------------
'Filtre par "Afficher tous les JTZ"
If CheckBox1.Value = True Then
Dim Plage As String
With Sheets("parametres")
Plage = .Range("AP8:AP" & .Range("AP50").End(xlUp).Row).Address
End With
ListBox1.RowSource = "parametres!" & Plage
End If
'-------------------------------------------------------------
'Filtre par "Auditeur"
For i = 1 To 2
If ComboBox1.Value = Sheets("parametres").Cells(i + 2, 3).Value Then
Auditeur = i
End If
Next
For x = 1 To 60
If Sheets("parametres").Cells(7 + x, 25 + Auditeur) = "X" Then
With ListBox1
.AddItem Sheets("parametres").Cells(7 + x, 42)
End With
End If
Next x
'-------------------------------------------------------------
'Filtre par "Audité"
For i = 1 To 5
If ComboBox2.Value = Sheets("parametres").Cells(i + 3, 3).Value Then
Audite = i
End If
Next
For x = 1 To 60
If Sheets("parametres").Cells(7 + x, 27 + Audite) = "X" Then
With ListBox1
.AddItem Sheets("parametres").Cells(7 + x, 42)
End With
End If
Next x
'-------------------------------------------------------------
'Filtre par "Validation d'habilitation"
For i = 1 To 9
If ComboBox3.Value = Sheets("parametres").Cells(6, 32 + i).Value Then
ValHab = i
End If
Next
For x = 1 To 60
If Sheets("parametres").Cells(7 + x, 32 + ValHab) = "X" Then
With ListBox1
.AddItem Sheets("parametres").Cells(7 + x, 42)
End With
End If
Next x
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
ListBox1.Visible = True
End Sub
Private Sub listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' Lien = ""
' With ListBox1
'' shname = .List(.ListIndex, 0)
' Set cel = Sheets("parametres").Range(.List(.ListIndex, 1))
' If cel.Hyperlinks.Count > 0 Then Lien = cel.Hyperlinks(1).Address
' End With
' If Lien <> "" Then
' Set ie = CreateObject("internetexplorer.application")
' ie.Visible = True
' ie.Navigate Lien
' Set ie = Nothing
' End If
End Sub |
Partager