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
| Option 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 envoyer vers planning ?", 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 CommandButton7_Click()
Dim S As String, i As Long, L As Long, Nbr As Long, k As Long
Dim C As Range, ResAdr As String, Sh As Worksheet
Dim tbPartiel()
Dim Tabl As Variant
Me.ListBox1.Clear
If TextBoxrec = "" Then
ListBox1.List = TbEntier
Exit Sub
End If
Set Sh = Sheets("Clients")
Tabl = Split(Me.TextBoxrec.Text, ";")
With Me.ListBox1
For Each Item In Tabl
Set C = Sh.[A:A].Find(Item, , , xlWhole)
If Not C Is Nothing Then
ResAdr = C.Address
Do
.AddItem C.Value
For i = 1 To 11
.List(.ListCount - 1, i) = C.Offset(, i)
Next i
Var = .List
Set C = Sh.[A:A].FindNext(C)
Loop While C.Address <> ResAdr
End If
Next Item
End With
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