Option Explicit

Private Sub Userform_Initialize()

'Récupérer nom du produit
Dim activeR As Integer
Dim CAS As Range
Dim CE As Range
Set CAS = Sheets("Sub").Range("B3:B" & Cells.Rows.Count)
Set CE = Sheets("Sub").Range("A3:A" & Cells.Rows.Count)

activeR = ActiveCell.Row

'Remplir listbox2
TextBox1.Value = Cells(activeR, 3).Value

'Remplir les ComboBox CAS et CE
Me.ComboBox1.List = CAS.Value
Me.ComboBox2.List = CE.Value

With Me.ComboBox3
            .AddItem "J"
            .AddItem "K"
            .AddItem "L"
            .AddItem "M"
            .AddItem "N"
            .AddItem "P"
            .AddItem "Q"
End With

With Me.ComboBox4
            .AddItem "J"
            .AddItem "K"
            .AddItem "L"
            .AddItem "M"
            .AddItem "N"
            .AddItem "P"
            .AddItem "Q"
End With

With Me.ComboBox5
            .AddItem "J"
            .AddItem "K"
            .AddItem "L"
            .AddItem "M"
            .AddItem "N"
            .AddItem "P"
            .AddItem "Q"
End With

With Me.ComboBox6
            .AddItem "J"
            .AddItem "K"
            .AddItem "L"
            .AddItem "M"
            .AddItem "N"
            .AddItem "P"
            .AddItem "Q"
End With

'Remplir listbox2 "substances déja saisies"
ListBox2.Clear
Dim i
For i = 3 To Sheets("Sub+").Range("C1002").End(xlUp).Row
ListBox2.AddItem Sheets("Sub+").Cells(i, 2).Value & " | " & Sheets("Sub+").Cells(i, 1).Value & " | " & Sheets("Sub+").Cells(i, 3).Value
Next i

'Récupérer substances déja saisies - Remplir listbox1
If ActiveCell <> "" Then

    Dim lgpos As Long
    Dim strchaine As String
    lgpos = 1   ' 1re position dans la chaîne
    strchaine = Sheets("Inventaire des produits").Range("AC" & activeR)
    
    With ActiveCell
        ' Boucle tant qu'il y a un caractère "R la ligne" (Line Field=LF)
        While (InStr(lgpos, strchaine, vbLf) > 0)
        ' Affichage de la chaîne en dans listbox de vérification
        Me.ListBox1.AddItem Mid(strchaine, lgpos, InStr(lgpos, strchaine, vbLf) - lgpos)
        ' Position du caractère LF
        lgpos = InStr(lgpos, strchaine, vbLf) + 1
        Wend
        'affichage de la fin de chaine
        Me.ListBox1.AddItem Mid(strchaine, lgpos, Len(strchaine) - lgpos + 1)
    End With
    
End If
End Sub

Private Sub ComboBox1_Change()

If ComboBox1 <> "" Then
ComboBox2.Value = ""
TextBox3.Value = ""
End If
End Sub

Private Sub ComboBox2_Change()

If ComboBox2 <> "" Then
ComboBox1.Value = ""
TextBox2.Value = ""
End If

End Sub

Private Sub TextBox2_Change()

If TextBox2 <> "" Then
ComboBox2.Value = ""
TextBox3.Value = ""
End If
End Sub

Private Sub TextBox3_Change()

If TextBox3 <> "" Then
ComboBox1.Value = ""
TextBox2.Value = ""
End If
End Sub

Private Sub CommandButton1_Click()
Dim cCompteur As Currency
Dim n

If ComboBox1 <> "" Then
Application.Cursor = xlWait

For n = 3 To 104735

If Sheets("Sub").Range("B" & n) = ComboBox1 Then
cCompteur = cCompteur + 1
Me.ListBox1.AddItem Sheets("Sub").Range("B" & n) & " | " & Sheets("Sub").Range("A" & n) & " | " & Sheets("Sub").Range("C" & n) & " | " & TextBox2.Value & " | " & ComboBox3.Value
End If

Next

Application.Cursor = xlDefault
If cCompteur = 0 Then MsgBox "Numéro CAS non repertorié." & vbLf & "Essayer le numéro CE ou utiliser l'onglet substances supplémentaires", vbInformation, "Numéro CAS non reconnu"
If cCompteur > 1 Then MsgBox "Il y a plusieurs substances pour ce numéro CAS" & vbLf & "Supprimer la ou les substances inutiles dans la liste des substances sélectionnées", vbExclamation, "Plusieurs substances pour ce numéro CAS"

Else
ListBox1.List = ListBox1.List

End If

TextBox2 = ""
ComboBox3 = ""
ComboBox1 = ""
End Sub

Private Sub CommandButton2_Click()
Dim cCompteur As Currency
Dim n
If ComboBox2 <> "" Then
Application.Cursor = xlWait
For n = 3 To 104735

If Sheets("Sub").Range("A" & n) = ComboBox2 Then
 cCompteur = cCompteur + 1
Me.ListBox1.AddItem Sheets("Sub").Range("B" & n) & " | " & Sheets("Sub").Range("A" & n) & " | " & Sheets("Sub").Range("C" & n) & " | " & TextBox3.Value & " | " & ComboBox4.Value
End If

Next

Application.Cursor = xlDefault
If cCompteur = 0 Then MsgBox "Numéro CE non repertorié." & vbLf & "Essayer le numéro CAS ou utiliser l'onglet substances non répertoriées", vbInformation, "Numéro CE non reconnu"

Else
ListBox1.List = ListBox1.List

End If

TextBox3 = ""
ComboBox4 = ""
ComboBox2 = ""
End Sub

Private Sub CommandButton3_Click()

Dim ligne
    
If TextBox4 = "" Then TextBox4 = "XXXXXXX"
If TextBox5 = "" Then TextBox5 = "YYYYYYYY"

If TextBox6 <> "" Then
Me.ListBox1.AddItem TextBox4 & " | " & TextBox5 & " | " & TextBox6 & " | " & TextBox7 & " | " & ComboBox5.Value

ligne = Sheets("Sub+").[C1002].End(xlUp).Row + 1

'--- Transfert Formulaire dans Liste Bonus
Sheets("Sub+").Cells(ligne, 1) = Me.TextBox5
Sheets("Sub+").Cells(ligne, 2) = Me.TextBox4
Sheets("Sub+").Cells(ligne, 3) = Me.TextBox6
     
Else
ListBox1.List = ListBox1.List

End If

TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
ComboBox5 = ""

End Sub

Private Sub CommandButton7_Click()
   If Me.ListBox2.ListIndex <> -1 Then
     Me.ListBox1.AddItem Me.ListBox2 & " | " & TextBox8 & " | " & ComboBox6.Value
  End If
  
  TextBox8 = ""
ComboBox6 = ""
End Sub

Private Sub CommandButton4_Click()
Dim n
Dim x
        
If ListBox1.ListCount <> 0 Then

For n = LBound(ListBox1.List) To UBound(ListBox1.List)
x = x & ListBox1.List(n) & vbLf
Next n

ActiveCell.Value = Left(x, Len(x) - 1)

Else
ActiveCell.Value = ""
End If

ActiveCell.Offset(0, 1).Select

Unload UFSubstances

End Sub

Private Sub CommandButton8_Click()

    Dim i As Long
    With Me.ListBox1
       For i = 0 To .ListCount
            If .Selected(i) Then
                .RemoveItem .ListIndex
            End If
       Next i
    End With
End Sub
Private Sub CommandButton9_Click()
Unload UFSubstances
End Sub