Dim Plg As Variant
Dim Nom As String, Valdeb As Variant, Valfin As Variant
Private Sub ComboBox1_Change()
Dim L As Integer, Nbre As Byte
Dim MonNumero As Variant, Plage As Range
'pour aller sur la bonne feuille
Nom = ComboBox1.Value
End Sub
Private Sub CommandButton1_Click()
Dim DerL As Integer, i As Byte
'si une textbox est vide fin procédure
For i = 1 To 12
If Controls("TextBox" & i).Value = "" Then
MsgBox "remplir toutes les textbox,svp"
Exit Sub
End If
Next i
'si nom est vide fin procédure
If Nom = "" Then Exit Sub
End If
With Sheets(Nom)
DerL = .Range("A65536").End(xlUp).Row + 1
.Range("A" & DerL).Value = Nom
.Range("B" & DerL).Value = TextBox1.Value
.Range("C" & DerL).Value = TextBox3.Value
.Range("D" & DerL).Value = TextBox4.Value
.Range("E" & DerL).Value = TextBox5.Value
.Range("W" & DerL).Value = TextBox6.Value
.Range("H" & DerL).Value = TextBox7.Value
.Range("J" & DerL).Value = TextBox8.Value
.Range("M" & DerL).Value = TextBox9.Value
.Range("O" & DerL).Value = TextBox10.Value
.Range("Q" & DerL).Value = TextBox11.Value
.Range("S" & DerL).Value = TextBox12.Value
.Range("U" & DerL).Value = TextBox13.Value
End With
'vide les textbox
For i = 1 To 13
Controls("TextBox" & i).Value = ""
Next i
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
Private Sub Label19_Click()
End Sub
Private Sub TextBox2_Change()
'année de début de chantier
Valdeb = TextBox24.Value
'année de fin de chantier
Valfin = TextBox25.Value
'visuel:textbox et label invisibles
'affaire sur deux années
If Valfin - Valdeb = 1 Then
TextBox14.Visible = True
TextBox15.Visible = True
TextBox19.Visible = True
TextBox20.Visible = True
Label10.Visible = True
Label11.Visible = True
Label15.Visible = True
Label16.Visible = True
'affaire sur trois années
ElseIf Valfin - Valdeb = 2 Then
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox19.Visible = True
TextBox20.Visible = True
TextBox21.Visible = True
Label10.Visible = True
Label11.Visible = True
Label12.Visible = True
Label15.Visible = True
Label16.Visible = True
Label17.Visible = True
'affaire sur quatre années
ElseIf Valfin - Valdeb = 3 Then
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
TextBox19.Visible = True
TextBox20.Visible = True
TextBox21.Visible = True
TextBox22.Visible = True
Label10.Visible = True
Label11.Visible = True
Label12.Visible = True
Label13.Visible = True
Label15.Visible = True
Label16.Visible = True
Label17.Visible = True
Label18.Visible = True
'affaire sur cinq années
ElseIf Valfin - Valdeb = 4 Then
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
TextBox18.Visible = True
TextBox19.Visible = True
TextBox20.Visible = True
TextBox21.Visible = True
TextBox22.Visible = True
TextBox23.Visible = True
Label10.Visible = True
Label11.Visible = True
Label12.Visible = True
Label13.Visible = True
Label14.Visible = True
Label15.Visible = True
Label16.Visible = True
Label17.Visible = True
Label18.Visible = True
Label19.Visible = True
End If
End Sub
Private Sub UserForm_Initialize()
TextBox1.MaxLength = 4
texbox1.AutoTab = True
TextBox2.MaxLength = 4
texbox2.AutoTab = True
Dim Plg As Variant
Dim i As Byte
'masque les textbox et les label inutiles(pour l'instant)
For i = 14 To 23
Controls("textbox" & i).Visible = False
Next i
For i = 10 To 19
Controls("label" & i).Visible = False
Next i
With Sheets("liste") 'tri croissant des noms
.Range("A1").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Plg = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
End With
ComboBox1.List = Plg
End Sub