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
| Sub ComboBox1_Change()
lieu = ComboBox1.Value
Feuil1.ListObjects("Tableau1").Range.AutoFilter Field:=3, Criteria1:=lieu
End Sub
Sub ComboBox2_Change()
Notes = ComboBox2.Value
Feuil1.ListObjects("Tableau1").Range.AutoFilter Field:=4, Criteria1:=Notes
End Sub
Sub ComboBox3_Change()
competences = ComboBox3.Value
Feuil1.ListObjects("Tableau1").Range.AutoFilter Field:=6, Criteria1:=competences
End Sub
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
nom = ComboBox4.Value
Feuil1.Select
Range("7:7").Find(nom, , xlValues, xlWhole, , , False).Select
ActiveCell.Select
colonne = ActiveCell.Column
ligne = ActiveCell.Row
Set graphdata = Sheets("saisie").Range(Cells(ligne + 3, colonne), Cells(ligne + 1949, colonne))
For Each w In graphdata
If w.EntireRow.Hidden = False Then datafound = True: Exit For
Next
If datafound = False Then Sheets(ComboBox4.Value).Select: MsgBox "Deux possibilités : Soit il n'y a pas de données pour cette sélection soit vous avez oublié d'enlever les filtres précédents": Exit Sub
Sheets(ComboBox4.Value).Select
Dim wsData As Worksheet, wsChart As Worksheet
Dim rngChart As Range
Dim objChart As ChartObject
Dim objLE As LegendEntry
ActiveSheet.unprotect Password:="toto"
Set wsData = Feuil1
Set wsChart = ActiveSheet
On Error Resume Next
wsChart.ChartObjects(1).Delete
On Error GoTo 0
Set objChart = wsChart.ChartObjects.Add _
(Left:=wsChart.Columns("c").Left, _
Top:=wsChart.Rows(9).Top, _
Width:=800, _
Height:=280)
With objChart.Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=graphdata
.HasTitle = True
.ChartTitle.Text = ComboBox3.Value & " - " & ComboBox4.Value & " - Niveau " & ComboBox2.Value & " - Lieu " & ComboBox1.Value
.HasLegend = False
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 20
End With
Unload Me
Set objChart = Nothing
Set rngChart = Nothing
Set wsChart = Nothing: Set wsData = Nothing
ActiveSheet.protect Password:="toto", DrawingObjects:=False, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
Feuil1.unprotect Password:="toto"
Feuil1.ListObjects("Tableau1").AutoFilter.ShowAllData
End Sub
Private Sub UserForm_Terminate()
Feuil1.protect Password:="toto"
End Sub |
Partager