1 pièce(s) jointe(s)
une proposition pour le formulaire...
Bonsoir,
je me suis un peu "amusé" à faire un formulaire à partir de votre fichier , j'ai proposé d'écrire les fiches dans la feuille 1 (de 3 en 3 ligne).
J'ai ajouté un spinbutton (pour passer d'une fiche à l'autre)et ecrit les deux procédures correspondante (down et up).
Vous pourez voir l'utilisation des If then else et l'utilisation des "booleens" par ex :
CheckBox1.Value = TextBox6.Value <> ""
CheckBox2.Value = TextBox11.Value <> ""
voir le code :
Code:
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222
| Private Sub Btn_Cancel_Click()
' TextBox1.Value = "" supprimez...
' TextBox2.Value = ""
' TextBox3.Value = ""
' TextBox4.Value = ""
' TextBox5.Value = ""
' TextBox6.Value = ""
' TextBox7.Value = ""
' TextBox8.Value = ""
' TextBox9.Value = ""
' TextBox10.Value = ""
Unload Me
End Sub
Private Sub CheckBox1_Change() 'procédure au changement
If CheckBox1.Value Then
CheckBox2.Visible = True
Label17.Visible = True
Label18.Visible = True
Label19.Visible = True
Label20.Visible = True
Label21.Visible = True
TextBox6.Visible = True
TextBox7.Visible = True
TextBox8.Visible = True
TextBox9.Visible = True
TextBox10.Visible = True
Else
If TextBox6.Value = "" Then
CheckBox2.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Label20.Visible = False
Label21.Visible = False
TextBox6.Visible = False
TextBox7.Visible = False
TextBox8.Visible = False
TextBox9.Visible = False
TextBox10.Visible = False
End If
End If
End Sub
Private Sub CheckBox2_Change()
CheckBox2.Visible = CheckBox1.Value
If CheckBox2.Value Then
Label22.Visible = True
Label23.Visible = True
Label24.Visible = True
Label25.Visible = True
Label26.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
Else
If TextBox11.Value = "" Then
Label22.Visible = False
Label23.Visible = False
Label24.Visible = False
Label25.Visible = False
Label26.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
End If
End If
End Sub
Private Sub SpinButton1_SpinDown() 'Pour changer de fiche
Dim ligne As Integer
Application.ScreenUpdating = False
ligne = CInt(Right(TextBox1.ControlSource, 1)) + 3
If Sheet1.Cells(ligne, 1) = "" And Sheet1.Cells(ligne - 3, 1) = "" Then Exit Sub 'ne pas dépasser deux fiches vides
TextBox1.ControlSource = "Sheet1!B" + CStr(ligne)
TextBox2.ControlSource = "Sheet1!C" + CStr(ligne)
TextBox3.ControlSource = "Sheet1!D" + CStr(ligne)
TextBox4.ControlSource = "Sheet1!E" + CStr(ligne)
TextBox5.ControlSource = "Sheet1!F" + CStr(ligne)
TextBox6.ControlSource = "Sheet1!B" + CStr(ligne + 1)
TextBox7.ControlSource = "Sheet1!C" + CStr(ligne + 1)
TextBox8.ControlSource = "Sheet1!D" + CStr(ligne + 1)
TextBox9.ControlSource = "Sheet1!E" + CStr(ligne + 1)
TextBox10.ControlSource = "Sheet1!F" + CStr(ligne + 1)
TextBox11.ControlSource = "Sheet1!B" + CStr(ligne + 2)
TextBox12.ControlSource = "Sheet1!C" + CStr(ligne + 2)
TextBox13.ControlSource = "Sheet1!D" + CStr(ligne + 2)
TextBox14.ControlSource = "Sheet1!E" + CStr(ligne + 2)
TextBox15.ControlSource = "Sheet1!F" + CStr(ligne + 2)
CheckBox1.Value = TextBox6.Value <> ""
CheckBox2.Value = TextBox11.Value <> ""
Application.ScreenUpdating = True
End Sub
Private Sub SpinButton1_SpinUp()
Dim ligne As Integer
Application.ScreenUpdating = False
ligne = CInt(Right(TextBox1.ControlSource, 1)) - 3
If ligne < 2 Then Exit Sub
TextBox1.ControlSource = "Sheet1!B" + CStr(ligne)
TextBox2.ControlSource = "Sheet1!C" + CStr(ligne)
TextBox3.ControlSource = "Sheet1!D" + CStr(ligne)
TextBox4.ControlSource = "Sheet1!E" + CStr(ligne)
TextBox5.ControlSource = "Sheet1!F" + CStr(ligne)
TextBox6.ControlSource = "Sheet1!B" + CStr(ligne + 1)
TextBox7.ControlSource = "Sheet1!C" + CStr(ligne + 1)
TextBox8.ControlSource = "Sheet1!D" + CStr(ligne + 1)
TextBox9.ControlSource = "Sheet1!E" + CStr(ligne + 1)
TextBox10.ControlSource = "Sheet1!F" + CStr(ligne + 1)
TextBox11.ControlSource = "Sheet1!B" + CStr(ligne + 2)
TextBox12.ControlSource = "Sheet1!C" + CStr(ligne + 2)
TextBox13.ControlSource = "Sheet1!D" + CStr(ligne + 2)
TextBox14.ControlSource = "Sheet1!E" + CStr(ligne + 2)
TextBox15.ControlSource = "Sheet1!F" + CStr(ligne + 2)
CheckBox1.Value = TextBox6.Value <> ""
CheckBox2.Value = TextBox11.Value <> ""
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize() 'à l'entrée du fichier
TextBox1.ControlSource = "Sheet1!B2" 'j'utilise les controlsource de chaque textbox
TextBox2.ControlSource = "Sheet1!C2"
TextBox3.ControlSource = "Sheet1!D2"
TextBox4.ControlSource = "Sheet1!E2"
TextBox5.ControlSource = "Sheet1!F2"
TextBox6.ControlSource = "Sheet1!B3"
TextBox7.ControlSource = "Sheet1!C3"
TextBox8.ControlSource = "Sheet1!D3"
TextBox9.ControlSource = "Sheet1!E3"
TextBox10.ControlSource = "Sheet1!F3"
TextBox11.ControlSource = "Sheet1!B4"
TextBox12.ControlSource = "Sheet1!C4"
TextBox13.ControlSource = "Sheet1!D4"
TextBox14.ControlSource = "Sheet1!E4"
TextBox15.ControlSource = "Sheet1!F4"
Label1.Visible = True
Label13.Visible = True
Label14.Visible = True
Label15.Visible = True
Label16.Visible = True
TextBox1.Visible = True
TextBox2.Visible = True
TextBox3.Visible = True
TextBox4.Visible = True
TextBox5.Visible = True
CheckBox1.Value = TextBox6.Value <> ""
If CheckBox1.Value Then
Label17.Visible = True
Label18.Visible = True
Label19.Visible = True
Label20.Visible = True
Label21.Visible = True
TextBox6.Visible = True
TextBox7.Visible = True
TextBox8.Visible = True
TextBox9.Visible = True
TextBox10.Visible = True
Else
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Label20.Visible = False
Label21.Visible = False
TextBox6.Visible = False
TextBox7.Visible = False
TextBox8.Visible = False
TextBox9.Visible = False
TextBox10.Visible = False
End If
CheckBox2.Value = TextBox11.Value <> ""
If CheckBox2.Value Then
Label22.Visible = True
Label23.Visible = True
Label24.Visible = True
Label25.Visible = True
Label26.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
Else
Label22.Visible = False
Label23.Visible = False
Label24.Visible = False
Label25.Visible = False
Label26.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
End If
End Sub |
et le fichier proposé :
Pièce jointe 166126
a vous...
geogeo