probleme Textbox

dran_200

XLDnaute Occasionnel
Bonjour à tous,
J'ai un soucis pour charger des valeurs dans des textbox. Pour 10 textbox j'ai ce lien :
ListBox2.AddItem
ListBox2.List(ListBox2.ListCount - 1, 0) = Cell.Offset(, 2)
ListBox2.List(ListBox2.ListCount - 1, 1) = Cell.Offset(, 4)
ListBox2.List(ListBox2.ListCount - 1, 2) = Cell.Offset(, 0)
ListBox2.List(ListBox2.ListCount - 1, 3) = Cell.Offset(, 8)
ListBox2.List(ListBox2.ListCount - 1, 4) = Cell.Offset(, 9)
ListBox2.List(ListBox2.ListCount - 1, 5) = Cell.Offset(, 10)
ListBox2.List(ListBox2.ListCount - 1, 6) = Cell.Offset(, 11)
ListBox2.List(ListBox2.ListCount - 1, 7) = Cell.Offset(, 6)
ListBox2.List(ListBox2.ListCount - 1, 8) = Cell.Offset(, 7)
ListBox2.List(ListBox2.ListCount - 1, 9) = Cell.Offset(, 5)
je charge mes textbox avec :
TextBox2 = ListBox2.List(ListBox2.ListIndex, 1)
etc.....
Jusque la tout fonctionne, le probleme est que j'ai besoin de créer plus de texbox et que le chemin ne fonctionne pas pour plus de 10 textbox :
ListBox2.List(ListBox2.ListCount - 1, 10) = Cell.Offset(, 5)
ça plante sur la valeur "10" ou plus !!!!
comment faire ?
merci pour votre aide
 

abtony

XLDnaute Impliqué
Re : probleme Textbox

re bonjour
J'ai toujour le même problème. Excel plante et ensuite je n'ai que la feuill BD. Je n'ai aucun code. Merci

Alors je te poste le code complet et tu le met sur ton fichier a toi !

Code:
Option Explicit
Public i As Integer
Public Produit As String
Public ref As Variant

Private Sub CommandButton8_Click()

    ComboBox1.Visible = False
    ListBox2.Visible = False
    ListBox3.Visible = False
    TextBox1.Visible = False
       
    CommandButton8.Visible = False

    TextBox2.Visible = True
    TextBox3.Visible = True
    TextBox8.Visible = True
    TextBox9.Visible = True
    TextBox10.Visible = True
    TextBox11.Visible = True
    TextBox12.Visible = True
    TextBox13.Visible = True
    TextBox14.Visible = True
      TextBox15.Visible = True
      TextBox16.Visible = True
    TextBox4.Visible = True
    TextBox5.Visible = True
    TextBox6.Visible = True
    TextBox7.Visible = True
    Label1.Visible = True
       
    TextBox1 = TextBox1
    
    TextBox2 = ListBox2.List(ListBox2.ListIndex, 3)
    TextBox3 = ListBox2.List(ListBox2.ListIndex, 4)
    TextBox4 = ListBox2.List(ListBox2.ListIndex, 5)
    TextBox5 = ListBox2.List(ListBox2.ListIndex, 6)
    TextBox6 = ListBox2.List(ListBox2.ListIndex, 7)
    TextBox7 = ListBox2.List(ListBox2.ListIndex, 8)
    TextBox8 = TextBox1
    TextBox9 = ListBox2.List(ListBox2.ListIndex, 2)
    TextBox10 = ListBox2.List(ListBox2.ListIndex, 9)
    TextBox12 = ListBox2.List(ListBox2.ListIndex, 12)
    TextBox14 = ListBox2.List(ListBox2.ListIndex, 13)
    TextBox13 = ListBox2.List(ListBox2.ListIndex, 14)
    TextBox11 = ListBox2.List(ListBox2.ListIndex, 15)
    TextBox16 = ListBox2.List(ListBox2.ListIndex, 16)
    TextBox15 = ListBox2.List(ListBox2.ListIndex, 17)
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Activate()
Dim tablo As Range
    Dim Cell As Range
    Dim Unique As New Collection
    Dim Valeur As Variant
    Dim j As Byte

    Sheets("BD").Select
    With Range("A1:l20000")
   .Sort Key1:=Range("b2"), Order1:=xlAscending, Header:=xlGuess
    .Sort Key1:=Range("c2"), Order1:=xlAscending, Header:=xlGuess
   .Sort Key1:=Range("a2"), Order1:=xlAscending, Header:=xlGuess
    End With

    ListBox2.Visible = False
    ListBox3.Visible = False
    TextBox1.Visible = False
  
    CommandButton8.Visible = False
    
    TextBox2.Visible = False
    TextBox3.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    TextBox6.Visible = False
    TextBox7.Visible = False
    TextBox8.Visible = False
    TextBox9.Visible = False
    TextBox10.Visible = False
    TextBox11.Visible = False
    TextBox12.Visible = False
    TextBox13.Visible = False
    TextBox14.Visible = False
    TextBox15.Visible = False
    TextBox16.Visible = False
    Label1.Visible = False
    
    i = Feuil1.Range("b65536").End(xlUp).Row

    On Error Resume Next
    For Each Cell In Feuil1.Range("a2:a" & i)
    If Cell <> "" Then Unique.add Cell, CStr(Cell)
    
    Next Cell
    On Error GoTo 0

    For Each Valeur In Unique
    USF1.ComboBox1.AddItem Valeur
    Next Valeur

    ListBox2.ColumnCount = 1
    ListBox2.ColumnWidths = "100"

    ListBox3.ColumnCount = 4
    ListBox3.ColumnWidths = "0;130;0;0"

End Sub

Private Sub ComboBox1_Change()

    Dim Valeur As String
    Dim Cell As Range
    Dim x As Byte, z As Byte

    ListBox2.Clear
    ListBox3.Clear
    
    ListBox2.Visible = True
   
    ListBox3.Visible = False
    TextBox1.Visible = False
    
    CommandButton8.Visible = False
    
    TextBox2.Visible = False
    TextBox3.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    TextBox6.Visible = False
    TextBox7.Visible = False
    TextBox8.Visible = False
    TextBox9.Visible = False
    TextBox10.Visible = False
    TextBox11.Visible = False
    TextBox12.Visible = False
    TextBox13.Visible = False
    TextBox14.Visible = False
    TextBox15.Visible = False
    TextBox16.Visible = False
    Label1.Visible = False
    
    If ComboBox1.ListIndex > -1 Then

    Valeur = ComboBox1.Value

    'For Each Cell In Feuil1.Range("a2:a" & i)
    
    'If Cell = Valeur Then
    'ListBox2.ColumnCount = 18
    
Dim t As Variant
t = Range("a1:r" & Cells(Rows.Count, 1).End(xlUp).Row): ListBox2.List = t
    'ListBox2.AddItem
    'ListBox2.List(ListBox2.ListCount - 1, 0) = Cell.Offset(, 2)
    'ListBox2.List(ListBox2.ListCount - 1, 1) = Cell.Offset(, 4)

    'ListBox2.List(ListBox2.ListCount - 1, 3) = Cell.Offset(, 8)
    'ListBox2.List(ListBox2.ListCount - 1, 4) = Cell.Offset(, 9)
    'ListBox2.List(ListBox2.ListCount - 1, 5) = Cell.Offset(, 10)
    'ListBox2.List(ListBox2.ListCount - 1, 6) = Cell.Offset(, 11)
    'ListBox2.List(ListBox2.ListCount - 1, 7) = Cell.Offset(, 6)
    'ListBox2.List(ListBox2.ListCount - 1, 8) = Cell.Offset(, 7)
    'ListBox2.List(ListBox2.ListCount - 1, 9) = Cell.Offset(, 5)
    'ListBox2.List(ListBox2.ListCount - 1, 2) = Cell.Offset(, 0)
       
    End If
    'Next Cell

    'End If

End Sub

Private Sub ListBox2_Click()

    Dim Valeur As String
    Dim Cell As Range
    Dim Tableau()
    Dim x As Byte, z As Byte

    ListBox3.Visible = True
    TextBox1.Visible = True
       
    CommandButton8.Visible = True
    
    TextBox2.Visible = False
    TextBox3.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    TextBox6.Visible = False
    TextBox7.Visible = False
    TextBox8.Visible = False
    TextBox9.Visible = False
    TextBox10.Visible = False
    TextBox11.Visible = False
    TextBox12.Visible = False
    TextBox13.Visible = False
    TextBox14.Visible = False
    Label1.Visible = False
    
    If ListBox2.ListIndex > -1 Then

    ReDim Preserve Tableau(24, x)
    Valeur = ListBox2.List(ListBox2.ListIndex, 1)
    For Each Cell In Feuil1.Range("a2:a" & i)
    If Cell = ComboBox1 And Cell.Offset(, 2) = ListBox2.Column(0) Then
    For z = 1 To 24
    Tableau(z - 1, x) = Feuil1.Cells(Cell.Row, z)
    Next z
        
    x = x + 1
    ReDim Preserve Tableau(24, x)
       
    End If

    Next Cell
    
    TextBox1 = ListBox2.List(ListBox2.ListIndex, 1)
    ListBox3.Column() = Tableau
  
           
    End If

End Sub


J'ai pas excel 2002 donc je ne pourrais pas t'aider d'avantage.
Mais essaye comme ça, et en créant les contrôles comme sur l'image avec le même nom.
 

abtony

XLDnaute Impliqué
Re : probleme Textbox

re bonjour
Toujours le même probleme , je n'arrive pas voir le code. merci pour ton aide


Code:
Option Explicit
Public i As Integer
Public Produit As String
Public ref As Variant

Private Sub CommandButton8_Click()

    ComboBox1.Visible = False
    ListBox2.Visible = False
    ListBox3.Visible = False
    TextBox1.Visible = False
       
    CommandButton8.Visible = False

    TextBox2.Visible = True
    TextBox3.Visible = True
    TextBox8.Visible = True
    TextBox9.Visible = True
    TextBox10.Visible = True
    TextBox11.Visible = True
    TextBox12.Visible = True
    TextBox13.Visible = True
    TextBox14.Visible = True
      TextBox15.Visible = True
      TextBox16.Visible = True
    TextBox4.Visible = True
    TextBox5.Visible = True
    TextBox6.Visible = True
    TextBox7.Visible = True
    Label1.Visible = True
       
    TextBox1 = TextBox1
    
    TextBox2 = ListBox2.List(ListBox2.ListIndex, 3)
    TextBox3 = ListBox2.List(ListBox2.ListIndex, 4)
    TextBox4 = ListBox2.List(ListBox2.ListIndex, 5)
    TextBox5 = ListBox2.List(ListBox2.ListIndex, 6)
    TextBox6 = ListBox2.List(ListBox2.ListIndex, 7)
    TextBox7 = ListBox2.List(ListBox2.ListIndex, 8)
    TextBox8 = TextBox1
    TextBox9 = ListBox2.List(ListBox2.ListIndex, 2)
    TextBox10 = ListBox2.List(ListBox2.ListIndex, 9)
    TextBox12 = ListBox2.List(ListBox2.ListIndex, 12)
    TextBox14 = ListBox2.List(ListBox2.ListIndex, 13)
    TextBox13 = ListBox2.List(ListBox2.ListIndex, 14)
    TextBox11 = ListBox2.List(ListBox2.ListIndex, 15)
    TextBox16 = ListBox2.List(ListBox2.ListIndex, 10)
    TextBox15 = ListBox2.List(ListBox2.ListIndex, 11)
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Activate()
Dim tablo As Range
    Dim Cell As Range
    Dim Unique As New Collection
    Dim Valeur As Variant
    Dim j As Byte

    Sheets("BD").Select
    With Range("A1:l20000")
   .Sort Key1:=Range("b2"), Order1:=xlAscending, Header:=xlGuess
    .Sort Key1:=Range("c2"), Order1:=xlAscending, Header:=xlGuess
   .Sort Key1:=Range("a2"), Order1:=xlAscending, Header:=xlGuess
    End With

    ListBox2.Visible = False
    ListBox3.Visible = False
    TextBox1.Visible = False
  
    CommandButton8.Visible = False
    
    TextBox2.Visible = False
    TextBox3.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    TextBox6.Visible = False
    TextBox7.Visible = False
    TextBox8.Visible = False
    TextBox9.Visible = False
    TextBox10.Visible = False
    TextBox11.Visible = False
    TextBox12.Visible = False
    TextBox13.Visible = False
    TextBox14.Visible = False
    TextBox15.Visible = False
    TextBox16.Visible = False
    Label1.Visible = False
    
    i = Feuil1.Range("b65536").End(xlUp).Row

    On Error Resume Next
    For Each Cell In Feuil1.Range("a2:a" & i)
    If Cell <> "" Then Unique.add Cell, CStr(Cell)
    
    Next Cell
    On Error GoTo 0

    For Each Valeur In Unique
    USF1.ComboBox1.AddItem Valeur
    Next Valeur

    ListBox2.ColumnCount = 1
    ListBox2.ColumnWidths = "100"

    ListBox3.ColumnCount = 4
    ListBox3.ColumnWidths = "0;130;0;0"

End Sub

Private Sub ComboBox1_Change()

    Dim Valeur As String
    Dim Cell As Range
    Dim x As Byte, z As Byte

    ListBox2.Clear
    ListBox3.Clear
    
    ListBox2.Visible = True
   
    ListBox3.Visible = False
    TextBox1.Visible = False
    
    CommandButton8.Visible = False
    
    TextBox2.Visible = False
    TextBox3.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    TextBox6.Visible = False
    TextBox7.Visible = False
    TextBox8.Visible = False
    TextBox9.Visible = False
    TextBox10.Visible = False
    TextBox11.Visible = False
    TextBox12.Visible = False
    TextBox13.Visible = False
    TextBox14.Visible = False
    TextBox15.Visible = False
    TextBox16.Visible = False
    Label1.Visible = False
    
    If ComboBox1.ListIndex > -1 Then

    Valeur = ComboBox1.Value

    For Each Cell In Feuil1.Range("a2:a" & i)
    
    If Cell = Valeur Then
    'ListBox2.ColumnCount = 18
    
Dim t As Variant
t = Range("c1:r" & Cells(Rows.Count, 1).End(xlUp).Row): ListBox2.List = t
    
       
    End If
    Next Cell

    End If

End Sub

Private Sub ListBox2_Click()

    Dim Valeur As String
    Dim Cell As Range
    Dim Tableau()
    Dim x As Byte, z As Byte

    ListBox3.Visible = True
    TextBox1.Visible = True
       
    CommandButton8.Visible = True
    
    TextBox2.Visible = False
    TextBox3.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    TextBox6.Visible = False
    TextBox7.Visible = False
    TextBox8.Visible = False
    TextBox9.Visible = False
    TextBox10.Visible = False
    TextBox11.Visible = False
    TextBox12.Visible = False
    TextBox13.Visible = False
    TextBox14.Visible = False
    Label1.Visible = False
    
    If ListBox2.ListIndex > -1 Then

    ReDim Preserve Tableau(24, x)
    Valeur = ListBox2.List(ListBox2.ListIndex, 1)
    For Each Cell In Feuil1.Range("a2:a" & i)
    If Cell = ComboBox1 And Cell.Offset(, 2) = ListBox2.Column(0) Then
    For z = 1 To 24
    Tableau(z - 1, x) = Feuil1.Cells(Cell.Row, z)
    Next z
        
    x = x + 1
    ReDim Preserve Tableau(24, x)
       
    End If

    Next Cell
    
    TextBox1 = ListBox2.List(ListBox2.ListIndex, 1)
    ListBox3.Column() = Tableau
  
           
    End If

End Sub
 

dran_200

XLDnaute Occasionnel
Re : probleme Textbox

RE Bonjour,
Le probleme est que lorsque je sélection par exemple "Fin" sur la 1er Combobox, sur la listbox il devrait avoir que "Carre". et pour "Large" que " Cercles et lignes". Actuellement pour le 1er choix sur "fin", la réponse correspond à toutes les valeurs de la colonne "C". Merci pour ton aide
 

Discussions similaires

Réponses
4
Affichages
254
Réponses
17
Affichages
994
Réponses
15
Affichages
579

Statistiques des forums

Discussions
312 858
Messages
2 092 876
Membres
105 547
dernier inscrit
Phil Moi