XL 2013 Modifier mon code

maval · 25 Juillet 2019

Bonjour

Je suis en train de faire une code et je me rencontre que mon code n'est pas très esthétique je voulais savoir si on pouvais le raccourcir.. oui je suis sur que l'on peux ?

Voici mon code:

VB:

Private Sub UserForm_Initialize()
Label1.Visible = False
Label2.Visible = False
Label3.Visible = False
Label4.Visible = False
TBx_1.Visible = False
TBx_2.Visible = False
TBx_3.Visible = False
TBx_4.Visible = False
TBx_5.Visible = False
TBx_6.Visible = False
TBx_7.Visible = False
TBx_8.Visible = False
TBx_9.Visible = False
TBx_10.Visible = False
TBx_11.Visible = False
TBx_12.Visible = False
TBx_13.Visible = False
TBx_14.Visible = False
TBx_15.Visible = False
TBx_16.Visible = False
TBx_17.Visible = False
TBx_18.Visible = False
TBx_19.Visible = False
TBx_20.Visible = False

Me.Width = 425
Me.Height = 83

End Sub

Private Sub OptionButton1_Click()
    If OptionButton1 = True Then
       
        ComboBox1.List = Feuil1.Range("B9:O" & Feuil1.Range("B" & Rows.Count).End(xlUp).Row).Value
    End If
Label1.Visible = True
Label2.Visible = False
Label3.Visible = False
Label4.Visible = True
Label5.Visible = False
Label6.Visible = False
Label7.Visible = True
Label8.Visible = False
Label9.Visible = False
Label10.Visible = True
Label11.Visible = False
Label12.Visible = False
Label13.Visible = True
Label14.Visible = False
Label15.Visible = False
Label16.Visible = True
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Label20.Visible = True
Label21.Visible = False
Label22.Visible = False
Label23.Visible = True
Label24.Visible = False
Label25.Visible = False
Label26.Visible = True
Label27.Visible = False
Label28.Visible = False
Label29.Visible = True
Label30.Visible = False
Label31.Visible = False
Label32.Visible = True
Label33.Visible = False
Label34.Visible = False
Label35.Visible = True
Label36.Visible = False
Label37.Visible = False
Label38.Visible = True
Label39.Visible = False
Label40.Visible = False
Label41.Visible = True
Label42.Visible = False
Label43.Visible = False
Label44.Visible = False
Label45.Visible = False
Label46.Visible = False
Label47.Visible = False
Label48.Visible = False
Label49.Visible = False
Label52.Visible = False
Label55.Visible = False
Label58.Visible = False
Label61.Visible = False

TBx_1.Visible = True
TBx_2.Visible = True
TBx_3.Visible = True
TBx_4.Visible = True
TBx_5.Visible = True
TBx_6.Visible = True
TBx_7.Visible = True
TBx_8.Visible = True
TBx_9.Visible = True
TBx_10.Visible = True
TBx_11.Visible = True
TBx_12.Visible = True
TBx_13.Visible = True
TBx_14.Visible = False
TBx_15.Visible = False
TBx_16.Visible = False
TBx_17.Visible = False
TBx_18.Visible = False
TBx_19.Visible = False
TBx_20.Visible = False

TBx_1.Value = ""
TBx_2.Value = ""
TBx_3.Value = ""
TBx_4.Value = ""
TBx_5.Value = ""
TBx_6.Value = ""
TBx_7.Value = ""
TBx_8.Value = ""
TBx_9.Value = ""
TBx_10.Value = ""
TBx_11.Value = ""
TBx_12.Value = ""
TBx_13.Value = ""
'TBx_14.Value = ""
TBx_15.Value = ""
TBx_16.Value = ""
TBx_17.Value = ""
TBx_18.Value = ""
TBx_19.Value = ""
TBx_20.Value = ""

ComboBox1.Value = ""

Me.Width = 425
Me.Height = 390
End Sub

Private Sub OptionButton2_Click()

    If OptionButton2 = True Then
       
        ComboBox1.List = Feuil1.Range("P9:AG" & Feuil1.Range("P" & Rows.Count).End(xlUp).Row).Value
    End If
Label1.Visible = False
Label2.Visible = True
Label3.Visible = False
Label4.Visible = False
Label5.Visible = True
Label6.Visible = False
Label7.Visible = False
Label8.Visible = True
Label9.Visible = False
Label10.Visible = False
Label11.Visible = True
Label12.Visible = False
Label13.Visible = False
Label14.Visible = True
Label15.Visible = False
Label16.Visible = False
Label17.Visible = True
Label18.Visible = False
Label19.Visible = False
Label20.Visible = False
Label21.Visible = True
Label22.Visible = False
Label23.Visible = False
Label24.Visible = True
Label25.Visible = False
Label26.Visible = True
Label27.Visible = True
Label28.Visible = False
Label29.Visible = False
Label30.Visible = True
Label31.Visible = False
Label32.Visible = False
Label33.Visible = True
Label34.Visible = False
Label35.Visible = False
Label36.Visible = True
Label37.Visible = False
Label38.Visible = False
Label39.Visible = True
Label40.Visible = False
Label41.Visible = False
Label42.Visible = True
Label43.Visible = False
Label44.Visible = True
Label45.Visible = True
Label46.Visible = False
Label47.Visible = True
Label48.Visible = True
Label49.Visible = False
Label52.Visible = False
Label55.Visible = False
Label58.Visible = False
Label61.Visible = False

TBx_1.Visible = True
TBx_2.Visible = True
TBx_3.Visible = True
TBx_4.Visible = True
TBx_5.Visible = True
TBx_6.Visible = True
TBx_7.Visible = True
TBx_8.Visible = True
TBx_9.Visible = True
TBx_10.Visible = True
TBx_11.Visible = True
TBx_12.Visible = True
TBx_13.Visible = True
TBx_14.Visible = True
TBx_15.Visible = True
TBx_16.Visible = True
TBx_17.Visible = True
TBx_18.Visible = False
TBx_19.Visible = False
TBx_20.Visible = False

TBx_1.Value = ""
TBx_2.Value = ""
TBx_3.Value = ""
TBx_4.Value = ""
TBx_5.Value = ""
TBx_6.Value = ""
TBx_7.Value = ""
TBx_8.Value = ""
TBx_9.Value = ""
TBx_10.Value = ""
TBx_11.Value = ""
TBx_12.Value = ""
TBx_13.Value = ""
ComboBox1.Value = ""

Me.Width = 425
Me.Height = 446
End Sub

Private Sub OptionButton3_Click()


    If OptionButton3 = True Then
       
        ComboBox1.List = Feuil1.Range("AH9:BB" & Feuil1.Range("AH" & Rows.Count).End(xlUp).Row).Value
    End If
Label1.Visible = False
Label2.Visible = False
Label3.Visible = True
Label4.Visible = False
Label5.Visible = False
Label6.Visible = True
Label7.Visible = False
Label8.Visible = False
Label9.Visible = True
Label10.Visible = False
Label11.Visible = False
Label12.Visible = True
Label13.Visible = False
Label14.Visible = False
Label15.Visible = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = True
Label19.Visible = True
Label20.Visible = False
Label21.Visible = False
Label22.Visible = True
Label23.Visible = False
Label24.Visible = False
Label25.Visible = True
Label26.Visible = False
Label27.Visible = False
Label28.Visible = True
Label29.Visible = False
Label30.Visible = False
Label31.Visible = True
Label32.Visible = False
Label33.Visible = False
Label34.Visible = True
Label35.Visible = False
Label36.Visible = False
Label37.Visible = True
Label38.Visible = False
Label39.Visible = False
Label40.Visible = True
Label41.Visible = False
Label42.Visible = False
Label43.Visible = True
Label44.Visible = False
Label45.Visible = False
Label46.Visible = True
Label47.Visible = False
Label48.Visible = False
Label49.Visible = True
Label52.Visible = True
Label55.Visible = True
Label58.Visible = True
Label61.Visible = True

TBx_1.Visible = True
TBx_2.Visible = True
TBx_3.Visible = True
TBx_4.Visible = True
TBx_5.Visible = True
TBx_6.Visible = True
TBx_7.Visible = True
TBx_8.Visible = True
TBx_9.Visible = True
TBx_10.Visible = True
TBx_11.Visible = True
TBx_12.Visible = True
TBx_13.Visible = True
TBx_14.Visible = True
TBx_15.Visible = True
TBx_16.Visible = True
TBx_17.Visible = True
TBx_18.Visible = True
TBx_19.Visible = True
TBx_20.Visible = True

TBx_1.Value = ""
TBx_2.Value = ""
TBx_3.Value = ""
TBx_4.Value = ""
TBx_5.Value = ""
TBx_6.Value = ""
TBx_7.Value = ""
TBx_8.Value = ""
TBx_9.Value = ""
TBx_10.Value = ""
TBx_11.Value = ""
TBx_12.Value = ""
TBx_13.Value = ""
TBx_14.Value = ""
TBx_15.Value = ""
TBx_16.Value = ""
TBx_17.Value = ""
TBx_18.Value = ""

ComboBox1.Value = ""
Me.Width = 425
Me.Height = 510
End Sub
Private Sub ComboBox1_Change()
TBx_1.Value = ComboBox1.Column(1)
TBx_2.Value = ComboBox1.Column(2)
TBx_3.Value = ComboBox1.Column(3)
TBx_4.Value = ComboBox1.Column(4)
TBx_5.Value = ComboBox1.Column(5)
TBx_6.Value = ComboBox1.Column(6)
TBx_7.Value = ComboBox1.Column(7)
TBx_8.Value = ComboBox1.Column(8)
TBx_9.Value = ComboBox1.Column(9)
TBx_10.Value = ComboBox1.Column(10)
TBx_11.Value = ComboBox1.Column(11)
TBx_12.Value = ComboBox1.Column(12)
TBx_13.Value = ComboBox1.Column(13)
'TBx_14.Value = ComboBox1.Column(14)
'TBx_15.Value = ComboBox1.Column(15)
'TBx_16.Value = ComboBox1.Column(16)
'TBx_17.Value = ComboBox1.Column(17)
'TBx_18.Value = ComboBox1.Column(18)
'TBx_19.Value = ComboBox1.Column(19)
'TBx_20.Value = ComboBox1.Column(20)
   
End Sub

Private Sub CommandButton1_Click()
   Unload Me
End Sub

Calvus · 25 Juillet 2019

Bonjour maval, le forum,

avec quelque chose dans ce style certainement :

VB:

For i = 1 To 3
With Me("Label" & i)
.Visible = False
.ForeColor = 14545386
.Height = 16
End With
Next i

Et ainsi de suite

Dis nous.

A+

maval · 25 Juillet 2019

Bonjour Calvus

Et ce code remplacerez tous label?

A+
Max

Calvus · 25 Juillet 2019

Re,

Oui, par groupe de tous labels ou textbox identiques.

VB:

For i = 1 To 4
With Me("Label" & i)
.Visible = False
End With
Next i

For i = 1 To 20
With Me("TBx_" & i)
.Visible = False
End With
Next i

A+

maval · 25 Juillet 2019

Re,

Sa veut dire qu'il faut mettre le code Trois fois ?

Calvus · 25 Juillet 2019

Re,

Regarde, j'ai édité mon message précédent.
5 lignes pour TBx au lieu de 20...

Robert · 25 Juillet 2019

Bonjour maval, bonjour le forum,

Peut-être comme ça :

VB:

Private Sub OptionButton1_Click()
If OptionButton1 = True Then
    ComboBox1.List = Feuil1.Range("B9:O" & Feuil1.Range("B" & Rows.Count).End(xlUp).Row).Value
End If
For I = 1 To 61
    Select Case I
        Case 1, 4, 7, 10, 13, 16, 20, 23, 26, 29, 32, 35, 38, 41
            Me.Controls("Label" & I).Visible = True
        Case 2, 3, 5, 6, 7, 8, 11, 12, 14, 15, 17, 18, 19, 21, 22, 24, 25, 27, 28, 30, 31, 33, 34, 36, 37, 39, 40, 52, 55, 58, 61
            Me.Controls("Label" & I).Visible = False
        Case 42 To 49
            Me.Controls("Label" & I).Visible = False
    End Select
Next I
For I = 1 To 20
    Select Case I
        Case 1 To 13
            Me.Controls("Tbx_" & I).Visible = True
            Me.Controls("Tbx_" & I).Value = ""
        Case 14 To 20
            Me.Controls("Tbx_" & I).Visible = False
            If I <> 14 Then Me.Controls("Tbx_" & I).Value = ""
    End Select
Next I
ComboBox1.Value = ""
Me.Width = 425
Me.Height = 390
End Sub

maval · 25 Juillet 2019

Bonjour Robert

Oui sur le premier Option Button sa fonctionne Nickel Je vais voir pour faire les deux autres pareil.

Merci beaucoup

Max

Effectuez une recherche sur Excel Downloads...

XL 2013 Modifier mon code

maval

XLDnaute Barbatruc

Calvus

XLDnaute Barbatruc

maval

XLDnaute Barbatruc

Calvus

XLDnaute Barbatruc

maval

XLDnaute Barbatruc

Calvus

XLDnaute Barbatruc

Robert

XLDnaute Barbatruc

maval

XLDnaute Barbatruc

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums