Bonjour,
Je cherche un moyen de simplifier le code suivant:
La seul différence c'est les forms qui sont appeler VCPTA ou VCPTB.
Il y a peut être d'autre simplification.
Merci.
Je cherche un moyen de simplifier le code suivant:
Code:
Select Case Tour
Case "TA"
VCPTA.Imglogo.Picture = TourTA.Controls("image" & Etages).Picture
VCPTA.Imglogo2.Picture = TourTA.Controls("image" & Etages).Picture
NumeroVCP = 1
Workbooks("temp.xls").Sheets("Feuil1").Select
For NumeroVCP = 1 To 90
On Error Resume Next
Sheets("Feuil1").Select
Columns("A:E").Select
Selection.AutoFilter
filtre = "CLVCP" & Tour & Format(Etages, "00") & Format(NumeroVCP, "000")
Selection.AutoFilter Field:=3, Criteria1:=filtre
Selection.AutoFilter Field:=2, Criteria1:=Format(VCPTA.CbxHeure.Value, "h:mm:ss")
derligne = Range("E2").End(xlDown).Row
VCPTA.Controls("Label" & NumeroVCP).Caption = Cells(derligne - 1, 5)
couleur = Cells(derligne - 2, 5)
If couleur = "OC_NUL" Then
VCPTA.Controls("Label" & NumeroVCP).BackColor = &HC0C000
VCPTA.Controls("Label" & NumeroVCP).Font.Bold = False
Else
VCPTA.Controls("Label" & NumeroVCP).BackColor = &H80C0FF
If couleur = "" Then VCPTA.Controls("Label" & NumeroVCP).BackColor = &HFFFFFF
End If
If couleur = "OC_STANDBY" Then
' VCPTB.Controls("Label" & NumeroVCP).ForeColor = &HC000&
VCPTA.Controls("Label" & NumeroVCP).Font.Bold = True
End If
If couleur = "OC_UNOCCUPIED" Then
'VCPTB.Controls("Label" & NumeroVCP).ForeColor = &HC000&
VCPTA.Controls("Label" & NumeroVCP).Font.Bold = True
End If
If couleur = "OC_OCCUPIED" Then
VCPTA.Controls("Label" & NumeroVCP).Font.Bold = False
End If
DECALAGE = Cells(derligne, 5)
Select Case DECALAGE
Case 3
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HC0&
Case 2
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HFF&
Case 1
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &H8080FF
Case 0
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HFFFFFF
Case -1
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HFFFF80
Case -2
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HFFFF00
Case -3
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HAB7332
Case ""
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HE0E0E0
End Select
If derligne < 0 Then
VCPTA.Controls("Label" & NumeroVCP).BackColor = &HFFFFFF
VCPTA.Controls("Label" & NumeroVCP + 100).BackColor = &HE0E0E0
VCPTA.Controls("Label" & NumeroVCP).Font.Bold = False
VCPTA.Controls("Label" & NumeroVCP).Caption = ""
End If
Next NumeroVCP
VCPTA.Caption = "Tour" & " " & Tour & " - " & "NIVEAU" & " " & Etages
VCPTA.LbTourEtage.Caption = "Tour" & " " & Tour & " - " & "NIVEAU" & " " & Etages
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
VCPTA.Show
Case "TB"
VCPTB.Imglogo.Picture = TourTA.Controls("image" & Etages).Picture
VCPTB.Imglogo2.Picture = TourTA.Controls("image" & Etages).Picture
NumeroVCP = 1
Workbooks("temp.xls").Sheets("Feuil1").Select
For NumeroVCP = 1 To 90
On Error Resume Next
Sheets("Feuil1").Select
Columns("A:E").Select
Selection.AutoFilter
filtre = "CLVCP" & Tour & Format(Etages, "00") & Format(NumeroVCP, "000")
Selection.AutoFilter Field:=3, Criteria1:=filtre
Selection.AutoFilter Field:=2, Criteria1:=Format(VCPTB.CbxHeure.Value, "h:mm:ss")
' derligne = Range("E2").End(xlDown).Row
'-----------------------------------------------------------
For i = (Cells(Rows.Count, 1).End(xlUp).Row) - Workbooks("VCP V15 interface.xls").Sheets("config").Range("A48") To Cells(Rows.Count, 1).End(xlUp).Row
If Rows(i).Hidden = False Then 'donc la ligne est visible
derligne = i
Exit For
End If
Next
'---------------------------------------------------------------------
VCPTB.Controls("Label" & NumeroVCP).Caption = Cells(derligne + 1, 5)
couleur = Cells(derligne, 5)
If couleur = "OC_NUL" Then
VCPTB.Controls("Label" & NumeroVCP).BackColor = &HC0C000
VCPTB.Controls("Label" & NumeroVCP).Font.Bold = False
Else
VCPTB.Controls("Label" & NumeroVCP).BackColor = &H80C0FF
End If
If couleur = "OC_STANDBY" Then
' VCPTB.Controls("Label" & NumeroVCP).ForeColor = &HC000&
VCPTB.Controls("Label" & NumeroVCP).Font.Bold = True
End If
If couleur = "OC_UNOCCUPIED" Then
'VCPTB.Controls("Label" & NumeroVCP).ForeColor = &HC000&
VCPTB.Controls("Label" & NumeroVCP).Font.Bold = True
End If
If couleur = "OC_OCCUPIED" Then
VCPTB.Controls("Label" & NumeroVCP).Font.Bold = False
End If
DECALAGE = Cells(derligne + 2, 5)
Select Case DECALAGE
Case 3
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HC0&
Case 2
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HFF&
Case 1
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &H8080FF
Case 0
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HFFFFFF
Case -1
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HFFFF80
Case -2
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HFFFF00
Case -3
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HAB7332
End Select
If derligne < 0 Then
VCPTB.Controls("Label" & NumeroVCP).BackColor = &HFFFFFF
VCPTB.Controls("Label" & NumeroVCP + 100).BackColor = &HE0E0E0
VCPTB.Controls("Label" & NumeroVCP).Font.Bold = False
VCPTB.Controls("Label" & NumeroVCP).Caption = ""
End If
Next NumeroVCP
VCPTB.Caption = "Tour" & " " & Tour & " - " & "NIVEAU" & " " & Etages
VCPTB.LbTourEtage.Caption = "Tour" & " " & Tour & " - " & "NIVEAU" & " " & Etages
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
VCPTB.Show
La seul différence c'est les forms qui sont appeler VCPTA ou VCPTB.
Il y a peut être d'autre simplification.
Merci.