Public Valid As Boolean
Private Sub ComboBox1_Click()
If ComboBox1.Text = "" Then Exit Sub
ActiveCell.Value = ComboBox1.Text
ComboBox1.Visible = False
Valid = True
ActiveCell.Select
End Sub
Private Sub ComboBox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim cel As Range
If KeyCode = 13 And Valid = False Then
ActiveCell.Value = ComboBox1.Text
ComboBox1.Visible = False
Valid = True
ActiveCell.Select
End If
If KeyCode = 18 Or KeyCode = 40 Then Exit Sub
Application.EnableEvents = False
[ListeMin].ClearContents
ActiveWorkbook.Names.Add "ListeMin", Feuil1.Range("B2")
For Each cel In Feuil1.Range("ListeOK")
If UCase(cel) Like UCase(ComboBox1.Text) & "*" Then
If Feuil1.Range("B2") = "" Then
Feuil1.Range("B2") = cel
Else
Feuil1.Range("B65000").End(xlUp).Offset(1, 0) = cel
End If
End If
Next cel
If Feuil1.Range("B3") <> "" Then
ActiveWorkbook.Names.Add "ListeMin", Feuil1.Range("B2:B" & Feuil1.Range("B2").End(xlDown).Row)
Else
ComboBox1.Text = Feuil1.Range("B2")
SendKeys "{enter}"
End If
ComboBox1.ListFillRange = "=ListeMin"
Application.EnableEvents = True
If [B3] <> "" Then SendKeys "%{down}"
End Sub
Private Sub ComboBox1_LostFocus()
ComboBox1.Value = ""
ComboBox1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'multi sélection non prise en compte
'****************************************************
If Target.Column <> 8 Then Exit Sub 'Ne Gérer que la colonne H (8)
'Définis la zone qui t'intéresse
'****************************************************
Valid = False
Feuil1.Range("A:A").Copy Destination:=Feuil1.Range("B:B")
ActiveWorkbook.Names.Add "ListeMin", Feuil1.Range("B2:B" & Feuil1.Range("B2").End(xlDown).Row)
SendKeys "a{BS}%{down}"
With ComboBox1
If ActiveCell <> "" Then .Text = ActiveCell Else .Text = ""
.Height = Target.Height + 4
.Width = Target.Width + 14
.Left = Target.Left - 1
.Font.Size = Int(.Height) - 5
.Top = Target.Top - 1
.Visible = True
.Activate
End With
Feuil1.Range("B:B").ClearContents
End Sub
:
