Merci de la réponse rapide mais je place cette fonction où exactement dans ce que j'ai déjà ?
Voici le code que j'ai:
If Not Intersect([d8:d65], Target) Is Nothing And Target.Count = 1 Then
codes = Split(Target, ":")
For i = LBound(codes) To UBound(codes)
If IsNumeric(codes(i)) Then
nature = Application.VLookup(Val(codes(i)), [codesMIP], 2, False)
Else
nature = Application.VLookup(codes(i), [codesMIP], 2, False)
End If
If Not IsError(nature) Then
tmp = tmp & nature & ", "
End If
Next i
'Application.EnableEvents = False
If tmp <> "" Then
Target.Offset(, 1) = Left(tmp, Len(tmp) - 2)
Else
Target.Offset(, 1) = ""
End If
'Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([M8:M65], Target) Is Nothing And Target.Count = 1 Then
a = Application.Transpose(Sheets("bd").Range("liste"))
Me.ComboBox1.List = a
Me.ComboBox1.Height = Target.Height + 3
Me.ComboBox1.Width = Target.Width
Me.ComboBox1.Top = Target.Top
Me.ComboBox1.Left = Target.Left
Me.ComboBox1 = Target
Me.ComboBox1.Visible = True
Me.ComboBox1.Activate
'Me.ComboBox1.DropDown ' ouverture automatique au clic dans la cellule (optionel)
Else
Me.ComboBox1.Visible = False
End If
'--- libellés codes
If Not Intersect([E8:E65], Target) Is Nothing And Target.Count = 1 Then
b = Application.Transpose(Sheets("codes").Range("LibellesCodes"))
Me.ComboBox2.List = b
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
End Sub
Private Sub ComboBox1_Change()
If Me.ComboBox1 <> "" And IsError(Application.Match(Me.ComboBox1, a, 0)) Then
Me.ComboBox1.List = Filter(a, Me.ComboBox1.Text, True, vbTextCompare)
Me.ComboBox1.DropDown
End If
ActiveCell.Value = Me.ComboBox1
End Sub
Private Sub ComboBox2_Change()
If Me.ComboBox2 <> "" And IsError(Application.Match(Me.ComboBox2, b, 0)) Then
Me.ComboBox2.List = Filter(b, Me.ComboBox2.Text, True, vbTextCompare)
Me.ComboBox2.DropDown
End If
ActiveCell.Value = Me.ComboBox2
p = Application.Match(ActiveCell, [LibellesCodes], 0)
If Not IsError(p) Then
Application.EnableEvents = False
ActiveCell.Offset(, -1) = Application.Index([codes], p)
Application.EnableEvents = True
End If
End Sub
Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.ComboBox1.List = a
Me.ComboBox1.Activate
Me.ComboBox1.DropDown
End Sub
Private Sub ComboBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.ComboBox2.List = b
Me.ComboBox2.Activate
Me.ComboBox2.DropDown
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Offset(columnOffset:=1).Select
End Sub
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Offset(columnOffset:=1).Select
End Sub