XL 2013 Macro qui plante

maval

XLDnaute Barbatruc
Bonsoir

J'ai un code qui fonctionne bien jusqu'à 13 textbox au delà sa plante

Mon Code:

VB:
Private Sub ComboBox1_Click()
 Dim elem As Byte
 For elem = 1 To 13
   With Me
   .Controls("TBx_" & elem).Value = .ComboBox1.Column(elem)
   End With
 Next
End Sub

Merci de votre aide

Max
 

maval

XLDnaute Barbatruc
Bonjour Job

Merci pour le code qui fonctionne très bien pour le premier OptionButton, je m'explique j'ai trois OptionButtons

Le 1er ouvre 15 Textbox, le 2éme 19 Textbox et le 3éme 22 TextBox

En sachant que la combobox se nome Cb1 et que textBox se nome TBx_

Je joint mon code au complet

VB:
Private Sub UserForm_Initialize()
For i = 1 To 4
    Me.Controls("Label" & i).Visible = False
Next i
For i = 1 To 20
    Me.Controls("TBx_" & i).Visible = False
Next i
Me.Width = 372
Me.Height = 99
End Sub

Private Sub Cb1_Click()
Dim elem As Byte
For elem = 1 To 22
    Controls("TBx_" & elem) = Cb1.Column(elem - 1)
Next
End Sub

Private Sub OptionButton1_Click()
If OptionButton1 = True Then
    Cb1.List = Feuil1.Range("B9:P" & Feuil1.Range("B" & Rows.Count).End(xlUp).Row).Value
End If
For i = 1 To 59
    Select Case i
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 57 'Label True
            Me.Controls("Label" & i).Visible = True
        Case 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58, 59 'Label False
            Me.Controls("Label" & i).Visible = False
        'Case 42 To 49
            'Me.Controls("Label" & i).Visible = False
    End Select
Next i
For i = 1 To 22
    Select Case i
        Case 1 To 15
            Me.Controls("Tbx_" & i).Visible = True 'TextBox Visible
            Me.Controls("Tbx_" & i).Value = ""
        Case 16 To 22
            Me.Controls("Tbx_" & i).Visible = False 'TextBox non Visible
            If i <> 14 Then Me.Controls("Tbx_" & i).Value = ""
    End Select
Next i
Cb1.Value = ""

Me.Width = 393
Me.Height = 437
End Sub

Private Sub OptionButton2_Click()
If OptionButton2 = True Then
    Cb1.List = Feuil1.Range("Q9:AI" & Feuil1.Range("Q" & Rows.Count).End(xlUp).Row).Value
End If
For i = 1 To 59
    Select Case i
        Case 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 58 'Label True
            Me.Controls("Label" & i).Visible = True
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 59 'Label False
            Me.Controls("Label" & i).Visible = False
        'Case 42 To 49
            'Me.Controls("Label" & i).Visible = False
    End Select
Next i
For i = 1 To 22
    Select Case i
        Case 1 To 22
            Me.Controls("Tbx_" & i).Visible = True 'TextBox Visible
            Me.Controls("Tbx_" & i).Value = ""
        Case 20 To 22
            Me.Controls("Tbx_" & i).Visible = False 'TextBox non Visible
            If i <> 14 Then Me.Controls("Tbx_" & i).Value = ""
    End Select
Next i
Cb1.Value = ""

Me.Width = 393
Me.Height = 501
End Sub

Private Sub OptionButton3_Click()
If OptionButton3 = True Then
    Cb1.List = Feuil1.Range("AJ9:BE" & Feuil1.Range("AJ" & Rows.Count).End(xlUp).Row).Value
End If
For i = 1 To 59
    Select Case i
        Case 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 59 'Label True
            Me.Controls("Label" & i).Visible = True
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 57, 58 'Label False
            Me.Controls("Label" & i).Visible = False
        'Case 42 To 49
            'Me.Controls("Label" & i).Visible = False
    End Select
Next i
For i = 1 To 22
    Select Case i
        Case 1 To 22
            Me.Controls("Tbx_" & i).Visible = True 'TextBox Visible
            Me.Controls("Tbx_" & i).Value = ""
       ' Case 18 To 20
           ' Me.Controls("Tbx_" & i).Visible = False 'TextBox non Visible
            'If i <> 14 Then Me.Controls("Tbx_" & i).Value = ""
    End Select
Next i
Cb1.Value = ""

Me.Width = 393
Me.Height = 587
End Sub
Private Sub CommandButton1_Click()
   Unload Me
End Sub

Merci d'avance
 

ChTi160

XLDnaute Barbatruc
Re
pour simplifier Lol
VB:
                  Case 1 To 15, 57 'Label True
                          Me.Controls("Label" & i).Visible = True
                  Case 16 To 59 'Label False                      
                          Me.Controls("Label" & i).Visible = False
ou
VB:
                  Case 1 To 15, 57 'Label True
                          Ok = True
                  Case 16 To 59 'Label False                      
                          Ok= False
              End Select
                          Me.Controls("Label" & i).Visible =Ok
Non testé !
jean marie
 

Discussions similaires

Réponses
12
Affichages
225
Réponses
0
Affichages
243
Réponses
6
Affichages
202

Statistiques des forums

Discussions
311 725
Messages
2 081 941
Membres
101 846
dernier inscrit
Silhabib