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
| Private Sub ComboBox1_Change()
temp = Workbooks("Base CPP Historique.xls").Worksheets("Comparatif").Cells(4, 1)
If ComboBox1 = "" Then
Workbooks("Base CPP Historique.xls").Worksheets("Comparatif").Cells(4, 1) = temp
Else
'ici on place la nouvelle valeur dans A4
Workbooks("Base CPP Historique.xls").Worksheets("Comparatif").Cells(4, 1).Value = ComboBox1.Value
ComboBox1.Value = Workbooks("Base CPP Historique.xls").Worksheets("Comparatif").Cells(4, 1).Value
End If
With Workbooks("Base CPP Historique.xls")
For p = 1 To Workbooks("Base CPP Historique.xls").Sheets.Count - 1
For q = 4 To .Worksheets(p).Range("A65536").End(xlUp).Row
If .Worksheets(p).Cells(q, 1).Value = .Worksheets("Comparatif").Cells(4, 1).Value Then
For r = 0 To 9
If .Worksheets("Comparatif").Cells(4, 2).Value = .Worksheets(p).Cells(q + r, 2).Value Then
.Worksheets(p).Range(.Worksheets(p).Cells(q + r, 3), .Worksheets(p).Cells(q + r, 35)).Copy .Worksheets("Comparatif").Range("C4")
Exit Sub
Else
End If
Next r
Else
End If
Next q
Next p
End With
End Sub
Private Sub ComboBox1_DropButtonClick()
Workbooks("Base CPP Historique.xls").Worksheets("Comparatif").ComboBox1.Clear
For v = 1 To Workbooks("Base CPP Historique.xls").Sheets.Count - 1
With Workbooks("Base CPP Historique.xls")
plage = .Worksheets(v).Range(.Worksheets(v).Cells(4, 1), .Worksheets(v).Cells(.Worksheets(v).Range("A65536").End(xlUp).Row, 1))
End With
For Each cell In plage
ComboBox1.AddItem cell
On Error GoTo gestionerr
Next
Next v
With Workbooks("Base CPP Historique.xls").Worksheets("Comparatif")
.Rows("4:20").RowHeight = 18
.Rows("4:20").HorizontalAlignment = 3
.Rows("4:20").VerticalAlignment = 2
'ajustement automatique des colonnes
.Range("C4:AI20").EntireColumn.AutoFit
End With
Exit Sub
gestionerr:
ComboBox1.AddItem plage
Resume Next
End Sub |
Partager