Dim a()
Private Sub ComboBox2_Change()
If Me.ComboBox2 <> "" And IsError(Application.Match(Me.ComboBox2, a, 0)) Then
Me.ComboBox2.List = Filter(a, Me.ComboBox2.Text, True, vbTextCompare)
Me.ComboBox2.DropDown
End If
ActiveCell.Value = Me.ComboBox2
End Sub
Private Sub ComboBox3_Change()
ActiveCell.Value = Me.ComboBox3
End Sub
Private Sub ComboBox2_DblClick(ByVal Cancel As msforms.ReturnBoolean)
Me.ComboBox2.List = a
Me.ComboBox2.Activate
Me.ComboBox2.DropDown
End Sub
Private Sub CommandButton2_Click()
UserForm3.Show
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([H7:H35], Target) Is Nothing Then
Application.OnKey Key:="~", procedure:="retour_colonneC"
End If
If Not Intersect([H4:H5], Target) Is Nothing Then
Application.OnKey Key:="~", procedure:="retour_colonneC2"
End If
If Not Intersect([C7:C35], Target) Is Nothing And Target.Count = 1 Then
a = Application.Transpose(Sheets("bdd").Range("liste"))
Me.ComboBox2.List = a
Me.ComboBox2.Height = Target.Height + 3
Me.ComboBox2.Width = Target.Width
Me.ComboBox2.Top = Target.Top
Me.ComboBox2.Left = Target.Left
Me.ComboBox2 = Target
Me.ComboBox2.Visible = True
Me.ComboBox2.Activate
'Me.ComboBox1.DropDown ' ouverture automatique au clic dans la cellule (optionel)
Else
Me.ComboBox2.Visible = False
End If
If Not Intersect([d7:d35], Target) Is Nothing And Target.Count = 1 Then
b = Range("liste_depots_bon_livraison")
Me.ComboBox3.List = b
Me.ComboBox3.Height = Target.Height + 3
Me.ComboBox3.Width = Target.Width
Me.ComboBox3.Top = Target.Top
Me.ComboBox3.Left = Target.Left
Me.ComboBox3 = Target
Me.ComboBox3.Visible = True
Me.ComboBox3.Activate
'Me.ComboBox1.DropDown 'ouverture automatique au clic dans la cellule (optionel)
Else
Me.ComboBox3.Visible = False
End If
If Not Intersect(Target, Worksheets("LIVRAISON").Range("C59:D64")) Is Nothing Then
Worksheets("LIVRAISON").Range("S2").Select
End If
End Sub
' code pour avertirtissement de doublon dans la colonne "C"
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Colonne As Integer
Dim Adresse As String
'On sort si plus d'une cellule a été modifiée
If Target.Count > 1 Then Exit Sub
'On sort si la cellule modifiée est vide
If Target.Value = "" Then Exit Sub
'Définit la colonne à vérifier (1=Colonne A, 2=colonne B ...etc...)
Colonne = 3
'Vérifie si c'est la colonne cible a été modifiée
If Target.Column = Colonne Then
'Recherche si la nouvelle donnée existe déjà dans la colonne.
Adresse = Columns(Colonne).Find(What:=Target.Value, After:=Target.Offset(1, 0), LookAt:=xlWhole, _
SearchDirection:=xlNext).Address
'Si l'adresse de cellule trouvée ne correspond pas à la cellule modifiée, cela
'signifie qu'il y a un doublon dans la colonne.
If Adresse <> Target.Address Then
MsgBox "La Réference '" & Target & "' Déjà saisie ", vbExclamation
End If
End If
End Sub