pb avec source d'une combobox

Option Explicit
Option Compare Text

Private Sub UserForm_Initialize()
Dim i As Integer

    For i = 1 To 50
        Me.ComboBox1.AddItem 'Founisseur N° ' & Format(i, '000')
    Next

End Sub


Private Sub CommandButton1_Click()
Dim Plage As Range, Cell As Range
Dim L As Integer
Dim NewSupplier As String
Dim Duplicate As Boolean

NewSupplier = Me.ComboBox1.Value
   
    With Sheets('Fournisseur')
        L = .Range('B65536').End(xlUp).Row + 1
        Set Plage = .Range(Range('B2'), .Range('B65536').End(xlUp))
    End With

    For Each Cell In Plage
        If CStr(NewSupplier) = CStr(Cell.Text) Then
            Duplicate = True
            Exit For
        End If
    Next

    If Duplicate Then
        MsgBox 'Duplication Fournisseur : ' & NewSupplier, vbCritical
    Else
        Sheets('Fournisseur').Range('B' & L) = NewSupplier
    End If
End Sub

Cliquez pour agrandir...