Option Explicit
Option Compare Text
Dim Plage As Range, C As Range, t, t2 As Variant
Dim Recherche, Adresse As String, x As Byte
Dim ligne, Ligne2 As Integer, N, N2 As Long
Dim I As Integer, J As Integer, Multi As Boolean, Flag As Boolean
Private Sub btsortir_Click()
UserFormBiblio.Hide
End Sub
Private Sub ComboBox1_Change()
Sheets(ComboBox1.Value).Select
UserForm_Initialize
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label13_Click()
End Sub
Private Sub Label17_Click()
End Sub
Private Sub Label18_Click()
End Sub
Private Sub Label23_Click()
End Sub
Private Sub ListBox1_Click()
Controls("Textbox6") = ""
Controls("Textbox7") = ""
Controls("Textbox8") = ""
For x = 1 To 5
Controls("Textbox" & x) = ListBox1.List(ListBox1.ListIndex, x - 1)
Next x
End Sub
Private Sub ListBox2_Click()
Controls("Textbox3") = ""
Controls("Textbox4") = ""
Controls("Textbox5") = ""
Controls("Textbox1") = ListBox2.List(ListBox2.ListIndex, 0)
Controls("Textbox2") = ListBox2.List(ListBox2.ListIndex, 1)
Controls("Textbox6") = ListBox2.List(ListBox2.ListIndex, 2)
Controls("Textbox7") = ListBox2.List(ListBox2.ListIndex, 3)
Controls("Textbox8") = ListBox2.List(ListBox2.ListIndex, 4)
End Sub
Private Sub TextBox11_Change()
End Sub
Private Sub Textbox2_Change()
End Sub
Private Sub TextBoxnoachat_Change()
End Sub
Private Sub TextBoxnocommande_Change()
End Sub
Private Sub TextBoxnofournisseur_Change()
End Sub
Private Sub TextBoxnomclient_Change()
End Sub
Private Sub TextBoxRechCode_Change()
'------------------- début no produit listbox1------------------'
If TextBoxRechCode = "" Or Right(TextBoxRechCode, 1) = " " Then Exit Sub
ListBox1.Clear
ListBox2.Clear
N = 0
Recherche = Split(TextBoxRechCode.Value, " ")
If InStr(1, TextBoxRechCode, " ", vbTextCompare) <> 0 Then Multi = True Else Multi = False
ligne = Worksheets("vente").Range("a" & "65536").End(xlUp).Row
Set Plage = Worksheets("vente").Range("a" & "1 : " & "a" & ligne)
With Plage
If Multi Then Set C = .Find(Recherche(0), LookAt:=xlPart) Else Set C = .Find(Recherche, LookAt:=xlPart)
If Not C Is Nothing Then
Adresse = C.Address
Do
Flag = True
If Multi Then
For J = 1 To UBound(Recherche)
If InStr(1, C, Recherche(J), vbTextCompare) = 0 Then
Flag = False
Exit For
End If
Next J
End If
If Flag = True Or Multi = False Then
ListBox1.AddItem C.Offset(0, 0), N
ListBox1.List(N, 0) = C
ListBox1.List(N, 1) = C.Offset(0, 1)
ListBox1.List(N, 2) = C.Offset(0, 2)
ListBox1.List(N, 3) = C.Offset(0, 3)
ListBox1.List(N, 4) = C.Offset(0, 4)
N = N + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> Adresse
End If
End With
For x = 1 To 8
Controls("Textbox" & x) = ""
Next x
If TextBoxRechCode = "" Then ListBox1.List = t
'------------------- fin no produit listbox1------------------'
'------------------- début no produit listbox2------------------'
N = 0
ligne = Worksheets("achat").Range("a" & "65536").End(xlUp).Row
Set Plage = Worksheets("achat").Range("a" & "1 : " & "a" & ligne)
With Plage
If Multi Then Set C = .Find(Recherche(0), LookAt:=xlPart) Else Set C = .Find(Recherche, LookAt:=xlPart)
If Not C Is Nothing Then
Adresse = C.Address
Do
Flag = True
If Multi Then
For J = 1 To UBound(Recherche)
If InStr(1, C, Recherche(J), vbTextCompare) = 0 Then
Flag = False
Exit For
End If
Next J
End If
If Flag = True Or Multi = False Then
ListBox2.AddItem C.Offset(0, 0), N
ListBox2.List(N, 0) = C
ListBox2.List(N, 1) = C.Offset(0, 1)
ListBox2.List(N, 2) = C.Offset(0, 2)
ListBox2.List(N, 3) = C.Offset(0, 3)
ListBox2.List(N, 4) = C.Offset(0, 4)
N = N + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> Adresse
End If
End With
For x = 1 To 8
Controls("Textbox" & x) = ""
Next x
If TextBoxRechCode = "" Then ListBox2.List = t2
'------------------- fin no produit listbox2------------------'
End Sub
Private Sub TextBoxRechLib_Change()
'------------------- début description listbox1------------------'
If TextBoxRechLib = "" Or Right(TextBoxRechLib, 1) = " " Then Exit Sub
ListBox1.Clear
ListBox2.Clear
N = 0
Recherche = Split(TextBoxRechLib.Value, " ")
If InStr(1, TextBoxRechLib, " ", vbTextCompare) <> 0 Then Multi = True Else Multi = False
ligne = Worksheets("vente").Range("b" & "65536").End(xlUp).Row
Set Plage = Worksheets("vente").Range("b" & "1 : " & "b" & ligne)
With Plage
If Multi Then Set C = .Find(Recherche(0), LookAt:=xlPart) Else Set C = .Find(Recherche, LookAt:=xlPart)
If Not C Is Nothing Then
Adresse = C.Address
Do
Flag = True
If Multi Then
For J = 1 To UBound(Recherche)
If InStr(1, C, Recherche(J), vbTextCompare) = 0 Then
Flag = False
Exit For
End If
Next J
End If
If Flag = True Or Multi = False Then
ListBox1.AddItem C.Offset(0, 0), N
ListBox1.List(N, 1) = C
ListBox1.List(N, 0) = C.Offset(0, -1)
ListBox1.List(N, 2) = C.Offset(0, 1)
ListBox1.List(N, 3) = C.Offset(0, 2)
ListBox1.List(N, 4) = C.Offset(0, 3)
N = N + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> Adresse
End If
End With
For x = 1 To 8
Controls("Textbox" & x) = ""
Next x
If TextBoxRechLib = "" Then ListBox1.List = t
'------------------- fin description listbox1------------------'
'------------------- debut description listbox2------------------'
N = 0
If InStr(1, TextBoxRechLib, " ", vbTextCompare) <> 0 Then Multi = True Else Multi = False
ligne = Worksheets("achat").Range("b" & "65536").End(xlUp).Row
Set Plage = Worksheets("achat").Range("b" & "1 : " & "b" & ligne)
With Plage
If Multi Then Set C = .Find(Recherche(0), LookAt:=xlPart) Else Set C = .Find(Recherche, LookAt:=xlPart)
If Not C Is Nothing Then
Adresse = C.Address
Do
Flag = True
If Multi Then
For J = 1 To UBound(Recherche)
If InStr(1, C, Recherche(J), vbTextCompare) = 0 Then
Flag = False
Exit For
End If
Next J
End If
If Flag = True Or Multi = False Then
ListBox2.AddItem C.Offset(0, 0), N
ListBox2.List(N, 1) = C
ListBox2.List(N, 0) = C.Offset(0, -1)
ListBox2.List(N, 2) = C.Offset(0, 1)
ListBox2.List(N, 3) = C.Offset(0, 2)
ListBox2.List(N, 4) = C.Offset(0, 3)
N = N + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> Adresse
End If
End With
For x = 1 To 8
Controls("Textbox" & x) = ""
Next x
If TextBoxRechLib = "" Then ListBox2.List = t2
'------------------- fin description listbox2------------------'
End Sub
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 5
ListBox1.ColumnWidths = "120;332;40;40;120"
t = Worksheets("vente").Range("a1:e" & Range("a65536").End(xlUp).Row)
ListBox1.List = t
ListBox2.ColumnCount = 5
ListBox2.ColumnWidths = "120;332;40;40;120"
t2 = Worksheets("achat").Range("a1:e" & Range("a65536").End(xlUp).Row)
ListBox2.List = t2
'ComboBox1.SetFocus
End Sub
Private Sub viderchamp_Click()
TextBoxRechCode.Value = ""
TextBoxRechLib.Value = ""
TextBoxnocommande.Value = ""
TextBoxnoclient.Value = ""
TextBoxnomclient.Value = ""
TextBoxnoachat.Value = ""
TextBoxnofournisseur.Value = ""
TextBoxnomfournisseur.Value = ""
End Sub