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 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
| Dim Graph As Graph.Chart
Dim oDataS As Object
Dim noCourbe As Integer
Dim NomBarre, Mois As String
Dim ValBarre As Double
Mois2 = Format(Now(), "mmmm")
Dim b As Variant
Dim LUBMTD As Double
Dim sSQL As String
Dim db As DAO.Database
Dim myrst As DAO.Recordset
Set db = CurrentDb
Dim val As Single
DoCmd.OpenForm "Graph-LU/ME-usine" ', , , , , acHidden
'Forms("Graph-LU/ME-usine").Visible = False
Set Graph = Forms("Graph-LU/ME-usine")(Graphique).Object.Application.Chart
Set oDataS = Graph.Application.DataSheet
sSQL = "SELECT [Graph LU/ME].[Nom Var], [Graph LU/ME].Valeur"
sSQL = sSQL & " FROM [Graph LU/ME];"
Set myrst = db.OpenRecordset(sSQL)
'si le recordset n'est pas vide
If Not myrst.EOF Then
' tant qu'il n'est pas vide
Do While Not myrst.EOF
nom = myrst.Fields("[Nom Var]").Value
val = myrst.Fields("[Valeur]").Value
'On compte le nombre de points (Barre) du graphique
nbCourbe = Graph.SeriesCollection.Count
For noCourbe = 1 To nbCourbe
NomBarre = oDataS.Cells(1, noCourbe + 1).Value
'ValBarre = oDataS.Cells(2, noCourbe + 1).Value
b = Split(NomBarre, " ")
With Graph.SeriesCollection(NomBarre)
.HasDataLabels = True
With .DataLabels
.Font.Size = 5
'.ShowValue = True
.NumberFormat = "##.#%"
.ShowSeriesName = False
.Position = xlLabelPositionCenter
End With
End With
If nom = NomBarre Then
If b(0) = "LU" Then
If b(1) = "MTD" Then
LUBMTD = DLookup("[LU B]", "Objectif-Cumulé", "[Mois]='" & Mois2 & "' ")
If Round(val * 100, 2) >= Round(LUBMTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(0, 255, 0)
End With
ElseIf Round(val * 100, 2) < Round(LUBMTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(255, 0, 0)
End With
End If
ElseIf b(1) = "YTD" Then
LUCYTD = DLookup("[LU C]", "Objectif-Cumulé", "[Mois]='" & Mois2 & "' ")
If Round(val * 100, 2) >= Round(LUCYTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(0, 255, 0)
End With
ElseIf Round(val * 100, 2) < Round(LUCYTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(255, 0, 0)
End With
End If
End If
ElseIf b(0) = "ME" Then
If b(1) = "MTD" Then
MEBMTD = DLookup("[ME B]", "Objectif-Cumulé", "[Mois]='" & Mois2 & "' ")
If Round(val * 100, 2) >= Round(MEBMTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(0, 255, 0)
End With
ElseIf Round(val * 100, 2) < Round(MEBMTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(255, 0, 0)
End With
End If
ElseIf b(1) = "YTD" Then
MECYTD = DLookup("[ME C]", "Objectif-Cumulé", "[Mois]='" & Mois2 & "' ")
If Round(val * 100, 2) >= Round(MECYTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(0, 255, 0)
End With
ElseIf Round(val * 100, 2) < Round(MECYTD * 100, 2) Then
With Graph.SeriesCollection(NomBarre)
.Interior.Color = RGB(255, 0, 0)
End With
End If
End If
End If
End If
Next
myrst.MoveNext
Loop
End If |
Partager