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
| Private Sub Combobox_article_Change()
Dim c, CSTDL, CSTDC, CSTFL, CSTFC As range
Dim TR As String, FirstAddress As String, Tbl() As String
Dim k As Integer
CSTDL = range("stockarticle").Row + 1
CSTDC = range("stockarticle").Column
CSTFL = range("stockarticle").Row + 1000
TR = ComboBox_Article.Value
If TR <> "" Then
With Worksheets("stock").range(Cells(CSTDL, CSTDC), Cells(CSTFL, CSTDC))
Set c = .Find(TR, LookIn:=xlValues, lookat:=xlPart)
If c Is Nothing Then
TextBox_reponse.Visible = True
ListBox_Article.Visible = False
Bouton_Ajouter.Visible = True
Valeurtrouve = TR & " N'appartient pas au stock, voulez vous l'ajouter"
TextBox_reponse.Value = Valeurtrouve
Else
TextBox_reponse.Visible = False
ListBox_Article.Visible = True
Bouton_Ajouter.Visible = False
FirstAddress = c.Address
Do
Set c = .FindNext(c)
k = k + 1
ReDim Preserve Tbl(1 To k)
Tbl(k) = c.Value
Loop While Not c Is Nothing And c.Address <> FirstAddress
With Me.ListBox_Article
.Clear
.List = Tbl
End With
End If
End With
End If
End Sub |
Partager