Listbox

[apdf1]

Bonjour

Dans mon formulaire j'ai des 10 TextBox et une Listbox comment alimenter ma listbox pour visionner les 10 colonnes de ma feuille et les envoyer vers les textbox.

Voici mon

Private Sub ComboBox1_Change()
Dim C As Range
Set C = Cells.Find(what:=Me.ComboBox1.Text, LookIn:=xlValues, lookat:=xlWhole)
Me.TextBox2 = Cells(C.Row, 3).Text 'nom
Me.TextBox3 = Cells(C.Row, 4).Text 'date
Me.TextBox4 = Cells(C.Row, 5).Text 'N° Facture
Me.TextBox5 = Cells(C.Row, 6).Text ' Adresse
Me.TextBox6 = Cells(C.Row, 7).Text 'CP
Me.TextBox7 = Cells(C.Row, 8).Text 'Ville
Me.TextBox8 = Cells(C.Row, 9).Text 'Tel
Me.TextBox9 = Cells(C.Row, 10).Text ' Fax
Me.TextBox10 = Cells(C.Row, 11).Text 'Mobile
Me.TextBox1 = Cells(C.Row, 2).Text 'Ref
End Sub

' Recherche par la listboxt
Private Sub ListBox1_Change()
Dim reste As Double
 ligne = ListBox1.ListIndex
  TextBox1 = Sheets("Feuil1").Cells(ligne + 9, 2) 'Ref
   TextBox2 = Sheets("Feuil1").Cells(ligne + 9, 3) 'nom
    TextBox3 = Sheets("Feuil1").Cells(ligne + 9, 4) 'date
     TextBox4 = Sheets("Feuil1").Cells(ligne + 9, 5) 'N° Facture
      TextBox5 = Sheets("Feuil1").Cells(ligne + 9, 6) 'adresse
       TextBox6 = Sheets("Feuil1").Cells(ligne + 9, 7) 'cp
        TextBox7 = Sheets("Feuil1").Cells(ligne + 9, 8) 'ville
         TextBox8 = Sheets("Feuil1").Cells(ligne + 9, 9) 'Tel
         TextBox9 = Sheets("Feuil1").Cells(ligne + 9, 10) 'Fax
         TextBox10 = Sheets("Feuil1").Cells(ligne + 9, 11) 'Mobile
         

lignea = ligne
 
End Sub

Private Sub CommandButton1_Click()
'Modifier
Dim C As Range
Set C = Cells.Find(what:=Me.TextBox1.Text, LookIn:=xlValues, lookat:=xlWhole)
Cells(C.Row, 3) = TextBox2.Value  'nom
Cells(C.Row, 4) = TextBox3.Value  'date
Cells(C.Row, 5) = TextBox4.Value  'N° Facture
Cells(C.Row, 6) = TextBox5.Value  'adresse
Cells(C.Row, 7) = TextBox6.Value  'cp
Cells(C.Row, 8) = TextBox7.Value  'ville
Cells(C.Row, 9) = TextBox8.Value 'Tel
Cells(C.Row, 10) = TextBox9.Value  'Fax
Cells(C.Row, 11) = TextBox10.Value  'Mobile
UserForm_Initialize
End Sub
' supprimer
Private Sub CommandButton3_Click()
Dim C As Range
Set C = Cells.Find(what:=Me.TextBox2.Text, LookIn:=xlValues, lookat:=xlWhole)
C.EntireRow.Delete
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
UserForm_Initialize
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim aa As Variant
    aa = Feuil1.Range("c9:l" & Feuil1.Range("B65536").End(xlUp).Row)
    ComboBox1.List = aa
    
     'listbox1
  Dim Plage As Range
With Sheets("Feuil1")
    Set Plage = .Range("c9:c" & .Range("B65536").End(xlUp).Row)
End With
ListBox1.List = Plage.Value
    
End Sub

Merci d'avance

Max

 

[Excel-lent]

Re : Listbox

Bonsoir Apdf1,

En attendant que quelqu'un vienne te dépatouiller ton soucis, voici déjà de quoi simplifier et raccourcir ton code

Private Sub ComboBox1_Change()
Dim C As Range
Set C = Cells.Find(what:=Me.ComboBox1.Text, LookIn:=xlValues, lookat:=xlWhole)

For i = 1 To 10
   Me.Controls("TextBox" & i) = Cells(C.Row, i + 1).Text
Next i

End Sub

' Recherche par la listboxt
Private Sub ListBox1_Change()
Dim reste As Double
ligne = ListBox1.ListIndex

For j = 1 To 10
   Controls("TextBox" & j) = Sheets("Feuil1").Cells(ligne + 9, j + 1)
Next j

lignea = ligne
 
End Sub

Private Sub CommandButton1_Click()
'Modifier
Dim C As Range
Set C = Cells.Find(what:=Me.TextBox1.Text, LookIn:=xlValues, lookat:=xlWhole)

For k = 2 To 10
   Cells(C.Row, k + 1) = Controls("TextBox" & k).Value
Next k

UserForm_Initialize
End Sub

' supprimer
Private Sub CommandButton3_Click()
Dim C As Range
Set C = Cells.Find(what:=Me.TextBox2.Text, LookIn:=xlValues, lookat:=xlWhole)
C.EntireRow.Delete

For l = 1 To 10
    Controls("TextBox" & l) = ""
Next l

UserForm_Initialize
End Sub

' ...

Bonne soirée

Cordialement

 

[Robert]

Re : Listbox

Bonsoir apdf, bonsoir le forum,

Et pourquoi pas ton fichier en pièce jointe aussi, pour qu'on n'ait pas à recreer ton envieronnement (UserForm, données, etc...) pour t'expliquer ce qui va pas dans la code ? Je suis sûr que tu obtiendras des réponses bien plus rapidement...

[Édition]
Bonsoir Excel-lent on s'est croisé...

 

[Robert]

Re : Listbox

Auto Censuré...