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 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182
| ption Base 1
Private Sub CommandButton1_Click()
With Sheets("Clients")
DerLg = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A" & DerLg + 1) = TextBoxfor
.Range("B" & DerLg + 1) = TextBoxdes
.Range("C" & DerLg + 1) = ComboBoxcon
.Range("D" & DerLg + 1) = TextBoxlot
.Range("E" & DerLg + 1) = TextBoxqua
.Range("F" & DerLg + 1) = CDate(TextBoxfab)
.Range("G" & DerLg + 1) = CDate(TextBoxfin)
.Range("H" & DerLg + 1) = ComboBoxzon
.Range("I" & DerLg + 1) = TextBoxall
.Range("J" & DerLg + 1) = TextBoxcom
.Range("K" & DerLg + 1) = TextBox7
.Range("L" & DerLg + 1) = TextBox5
Set plg = .Range("A2:L" & .Range("A" & .Rows.Count).End(xlUp).Row)
plg.Columns.AutoFit
Me.Width = ListBox1.Width + 20
TbEntier = plg
End With
End Sub
Private Sub CommandButton2_Click()
Dim DerLig As Long
If MsgBox("Voulez vous modifier l'article ?", 36, "Création") = vbYes Then
DerLig = Sheets("Feuil3").Range("A65000").End(xlUp).Row + 1
Range(Cells(DerLig - 1, 1), Cells(DerLig - 1, 40)).Copy
Cells(DerLig, 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Sheets("Feuil3").Cells(DerLig, 1) = TextBoxfor.Value
Sheets("Feuil3").Cells(DerLig, 2) = TextBoxdes.Value
Sheets("Feuil3").Cells(DerLig, 3) = ComboBoxcon.Value
Sheets("Feuil3").Cells(DerLig, 4) = TextBoxlot.Value
Sheets("Feuil3").Cells(DerLig, 5) = TextBoxqua.Value
Sheets("Feuil3").Cells(DerLig, 6) = CDate(TextBoxfab.Value)
Sheets("Feuil3").Cells(DerLig, 7) = CDate(TextBoxfin.Value)
'Sheets("Feuil3").Cells(DerLig, 8) = ComboBoxzon.Value
Sheets("Feuil3").Cells(DerLig, 8) = TextBoxall.Value
Sheets("Feuil3").Cells(DerLig, 9) = ComboBoxlig
Sheets("Feuil3").Cells(DerLig, 10) = CDate(TextBoxcon.Value)
End If
End Sub
Private Sub CommandButton4_Click()
Unload Me
End Sub
Private Sub CommandButton5_Click()
Me.ListBox1.RemoveItem Me.ListBox1.ListIndex
End Sub
Private Sub CommandButton6_Click()
'
' effacer Macro
' efface les cellule de la feuille planning
'
'
Range("A2:J1519").Select
Selection.ClearContents
End Sub
Private Sub Label1_Click()
End Sub
Private Sub TextBoxcom_Change()
End Sub
Private Sub TextBoxrec_Change()
Dim S As String, i As Long, L As Long, Nbr As Long, k As Long
Dim tbPartiel()
nbrecol = 12
If TextBoxrec = "" Then
ListBox1.List = TbEntier
Else
L = 1
ReDim Preserve tbPartiel(UBound(TbEntier, 1), nbrecol)
For i = 1 To UBound(TbEntier, 1)
For k = 1 To nbrecol
If UCase(TbEntier(i, k)) Like "*" & UCase(TextBoxrec) & "*" Then
For Nbr = 1 To 12
tbPartiel(L, Nbr) = TbEntier(i, Nbr)
Next Nbr
L = L + 1
Exit For
End If
Next k
Next i
Me.Repaint
ListBox1.List = tbPartiel
End If
End Sub
Private Sub UserForm_Initialize()
Dim cw
With Sheets("Clients")
Set plg = .Range("A2:L" & .Range("A" & .Rows.Count).End(xlUp).Row)
plg.Columns.AutoFit
ListBox1.Width = 20 + plg.Width
Me.Width = ListBox1.Width + 20
TbEntier = plg
End With
With ListBox1
cw = ""
.ColumnCount = plg.Columns.Count
.List = TbEntier
For i = 1 To .ColumnCount
cw = cw & plg.Columns(i).Width & ";"
Next
.ColumnWidths = cw
.ListIndex = -1
End With
ComboBoxcon.Clear
ComboBoxzon.Clear
ComboBoxlig.Clear
ComboBoxcon.AddItem "Conforme"
ComboBoxcon.AddItem "Non-Conforme"
ComboBoxcon.AddItem "En Attente"
ComboBoxzon.AddItem "Conforme"
ComboBoxzon.AddItem "Non-Conforme"
ComboBoxzon.AddItem "Preparation"
ComboBoxzon.AddItem "Controle"
ComboBoxzon.AddItem "Recontrole"
ComboBoxlig.AddItem "Ratio 1"
ComboBoxlig.AddItem "Ratio 2"
ComboBoxlig.AddItem "Ratio 3"
ComboBoxlig.AddItem "Ratio 4"
ComboBoxlig.AddItem "Ratio 5"
ComboBoxlig.AddItem "Ratio 6"
ComboBoxlig.AddItem "MLB"
ComboBoxlig.AddItem "KALIX"
ComboBoxlig.AddItem "Stoppil 3"
ComboBoxlig.AddItem "Stoppil 4"
ComboBoxlig.AddItem "Serac 2"
ComboBoxlig.AddItem "Serac 3"
ComboBoxlig.AddItem "Stoppil 2"
End Sub
Sub ListBox1_Click()
Dim i As Integer
i = ListBox1.ListIndex
Row = ListBox1.ListIndex + 2
TextBoxfor.Text = ListBox1.List(i, 0)
TextBoxdes.Text = ListBox1.List(i, 1)
ComboBoxcon.Text = ListBox1.List(i, 2)
TextBoxlot.Text = ListBox1.List(i, 3)
TextBoxqua.Text = ListBox1.List(i, 4)
TextBoxfab.Text = CDate(ListBox1.List(i, 5))
TextBoxfin.Text = CDate(ListBox1.List(i, 6))
ComboBoxzon.Text = ListBox1.List(i, 7)
TextBoxall.Text = ListBox1.List(i, 8)
TextBoxcom.Text = ListBox1.List(i, 9)
End Sub |
Partager