Dim Rowselected As Long ' Déclarer Rowselected globalement

Private Sub UserForm_Initialize()

'***********FILTRES**************
    'Désactiver les Combobox
    ComboBox11.Enabled = False
    ComboBox12.Enabled = False

    ' Remplir ComboBox10
    Dim cella As Range
    Dim uniqueValuesa As Collection
    Set uniqueValuesa = New Collection

    On Error Resume Next
    For Each cella In ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Nom Domaine]")
        If cella.Value <> "" Then
            uniqueValuesa.Add cella.Value, CStr(cella.Value)
        End If
    Next cella
    On Error GoTo 0

    Dim i As Integer
    Dim valuesArraya() As Variant

    ReDim valuesArraya(1 To uniqueValuesa.Count)
    For i = 1 To uniqueValuesa.Count
        valuesArraya(i) = uniqueValuesa(i)
    Next i

    ComboBox10.List = valuesArraya
'***********ROBUSTESSE**************
    ' Désactiver les TextBox initialement
    TextBox1.Enabled = False
    TextBox3.Enabled = False
    TextBox4.Enabled = False
    TextBox5.Enabled = False
    TextBox6.Enabled = False
    TextBox7.Enabled = False
    TextBox8.Enabled = False
    TextBox9.Enabled = False

    ' Remplir ComboBox5
    Dim cellb As Range
    Dim uniqueValuesb As Collection
    Set uniqueValuesb = New Collection

    On Error Resume Next
    For Each cellb In ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[REFERENCE]")
        If cellb.Value <> "" Then
            uniqueValuesb.Add cellb.Value, CStr(cellb.Value)
        End If
    Next cellb
    On Error GoTo 0

    Dim j As Integer
    Dim valuesArrayb() As Variant

    ReDim valuesArrayb(1 To uniqueValuesb.Count)
    For j = 1 To uniqueValuesb.Count
        valuesArrayb(j) = uniqueValuesb(j)
    Next j

    ComboBox5.List = valuesArrayb
End Sub

Private Sub ComboBox5_Change()
    ' Débloquer et remplir tous les éléments
    TextBox1.Enabled = True
    TextBox3.Enabled = True
    TextBox4.Enabled = True
    TextBox5.Enabled = True
    TextBox6.Enabled = True
    TextBox7.Enabled = True
    TextBox8.Enabled = True
    TextBox9.Enabled = True

    Dim foundCell As Range

    ' Trouver la cellule correspondant à la valeur sélectionnée dans ComboBox5
    Set foundCell = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[REFERENCE]").Find(What:=ComboBox5.Value, LookIn:=xlValues, LookAt:=xlWhole)

    If Not foundCell Is Nothing Then
        Rowselected = foundCell.Row ' Stocker le numéro de ligne globalement

        ' Afficher un message avec le numéro de ligne
        'MsgBox "Numéro de ligne sélectionnée : " & Rowselected

        ' Remplir les TextBox avec les valeurs correspondantes
        TextBox1.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Avancement]").Cells(Rowselected - 10, 1).Value
        TextBox2.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Commentaire]").Cells(Rowselected - 10, 1).Value
        TextBox3.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Coefficient]").Cells(Rowselected - 10, 1).Value
        TextBox4.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Définition CAO dans DMU]").Cells(Rowselected - 10, 1).Value
        TextBox5.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Robustesse conception]").Cells(Rowselected - 10, 1).Value
        TextBox6.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Validation calcul ]").Cells(Rowselected - 10, 1).Value
        TextBox7.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Validation Implantation avec métiers partenaires ]").Cells(Rowselected - 10, 1).Value
        TextBox8.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Index]").Cells(Rowselected - 10, 1).Value
        TextBox9.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Ind.]").Cells(Rowselected - 10, 1).Value
        TextBox10.Value = ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[DesignationFrance]").Cells(Rowselected - 10, 1).Value
    Else
        MsgBox "Valeur non trouvée dans la colonne REFERENCE."
    End If
End Sub

Private Sub CommandButton1_Click()
    If Rowselected > 0 Then ' Vérifier que Rowselected a été défini
        ' Remplir les Cellules avec les TextBox
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Commentaire]").Cells(Rowselected - 10, 1).Value = TextBox2.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Coefficient]").Cells(Rowselected - 10, 1).Value = TextBox3.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Définition CAO dans DMU]").Cells(Rowselected - 10, 1).Value = TextBox4.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Robustesse conception]").Cells(Rowselected - 10, 1).Value = TextBox5.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Validation calcul ]").Cells(Rowselected - 10, 1).Value = TextBox6.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Validation Implantation avec métiers partenaires ]").Cells(Rowselected - 10, 1).Value = TextBox7.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Index]").Cells(Rowselected - 10, 1).Value = TextBox8.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[Ind.]").Cells(Rowselected - 10, 1).Value = TextBox9.Value
        ThisWorkbook.Sheets("QryNomenclatureComplete").Range("BOM[DesignationFrance]").Cells(Rowselected - 10, 1).Value = TextBox10.Value

        ' Actualiser les valeurs des TextBox et ComboBox
        ComboBox5_Change
    Else
        MsgBox "Aucune ligne sélectionnée."
    End If
End Sub

Private Sub ComboBox10_Change()
    ' Débloquer ComboBox11 et remplir avec les valeurs correspondantes
    ComboBox11.Enabled = True

    ' Remplir ComboBox11
    Dim cell As Range
    Dim uniqueValues As Collection
    Set uniqueValues = New Collection

    On Error Resume Next
    For Each cell In ThisWorkbook.Sheets("QryNomenclatureComplete").Range("E:E")
        If cell.Value = ComboBox10.Value Then
            Dim correspondingCell As Range
            Set correspondingCell = cell.Offset(0, 1) ' La colonne F est la suivante

            If correspondingCell.Value <> "" Then
                uniqueValues.Add correspondingCell.Value, CStr(correspondingCell.Value)
            End If
        End If
    Next cell
    On Error GoTo 0

    If uniqueValues.Count > 0 Then
        Dim i As Integer
        Dim valuesArray() As Variant

        ReDim valuesArray(1 To uniqueValues.Count)
        For i = 1 To uniqueValues.Count
            valuesArray(i) = uniqueValues(i)
        Next i

        ComboBox11.List = valuesArray
    Else
        ComboBox11.Clear
    End If
End Sub

Private Sub ComboBox11_Change()
    ' Débloquer ComboBox12 et remplir avec les valeurs correspondantes
    ComboBox12.Enabled = True

    ' Remplir ComboBox12
    Dim cell As Range
    Dim uniqueValues As Collection
    Set uniqueValues = New Collection

    On Error Resume Next
    For Each cell In ThisWorkbook.Sheets("QryNomenclatureComplete").Range("F:F")
        If cell.Value = ComboBox11.Value Then
            Dim correspondingCell As Range
            Set correspondingCell = cell.Offset(0, 1) ' Suppose que la colonne suivante contient les valeurs pour ComboBox12

            If correspondingCell.Value <> "" Then
                uniqueValues.Add correspondingCell.Value, CStr(correspondingCell.Value)
            End If
        End If
    Next cell
    On Error GoTo 0

    If uniqueValues.Count > 0 Then
        Dim i As Integer
        Dim valuesArray() As Variant

        ReDim valuesArray(1 To uniqueValues.Count)
        For i = 1 To uniqueValues.Count
            valuesArray(i) = uniqueValues(i)
        Next i

        ComboBox12.List = valuesArray
    Else
        ComboBox12.Clear
    End If
End Sub