Liste deroulante

OLFA MEZNI

XLDnaute Nouveau
bonjour tt le monde j ai un probleme au niveau de mon fichier exel je veux utiliser deux drop down list
Dim a()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([D2:D500], Target) Is Nothing And Target.Count = 1 Then
a = Application.Transpose(Sheets("feuil2").Range("liste").Value)
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
End Sub

Private Sub ComboBox1_Change()
If Me.ComboBox1 <> "" And IsError(Application.Match(Me.ComboBox1, a, 0)) Then
Me.ComboBox1.List = Filter(E, Me.ComboBox1.Text, True, vbTextCompare)
Me.ComboBox1.DropDown
Else
ActiveCell = Me.ComboBox1
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 ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Offset(1).Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("F:F"), Target)
xOffsetColumn = -5
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

Dim a()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([E2:E500], Target) Is Nothing And Target.Count = 1 Then
a = Application.Transpose(Sheets("feuil2").Range("liste").Value)
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
End Sub

Private Sub ComboBox1_Change()
If Me.ComboBox1 <> "" And IsError(Application.Match(Me.ComboBox1, a, 0)) Then
Me.ComboBox1.List = Filter(E, Me.ComboBox1.Text, True, vbTextCompare)
Me.ComboBox1.DropDown
Else
ActiveCell = Me.ComboBox1
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 ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Offset(1).Select
End Sub
jai utiliser ce code sur la colonne D et je veux appliquer le meme sur la colonne E mais a chaque fois il me donne error
et aussi un pbl au niveau de deplacement de cursseur pour ce code si je l applique il au niveau de la cellule a la quelle il exxite la liste deroute si je chois a partir de ma liste automatiquement le curseur se deplace dans la cellule apres en bas pas a gauche.
svp aidez moi et merci d avance
 

Pièces jointes

  • olfa.xlsm
    42.7 KB · Affichages: 8