Option Explicit
Private Sub UserForm_Initialize()
'Alimentation des combobox
Me.ComboBox1.RowSource = "LIST_FILIERES"
Me.ComboBox2.RowSource = "LIST_ACHETEUR"
Me.ComboBox3.RowSource = "LIST_RENOUV"
Me.ComboBox4.RowSource = "LIST_ETAT"
Me.ComboBox5.RowSource = "LIST_STRATE"
End Sub
Private Sub CommandButton2_Click()
'Bouton quitter
Unload Me
End Sub
Private Sub CommandButton1_Click()
Dim ligne As Integer
'Bouton Valider
'--- Positionnement dans la base
ligne = Sheets("DAL").[B65000].End(xlUp).Row + 1
'--- Transfert Formulaire dans DAL
With Sheets("DAL")
Cells(ligne, 2) = Me.ComboBox1 '2 = colonne B Filières
Cells(ligne, 3) = Me.TextBox1 '3 = colonne C Segments
Cells(ligne, 4) = Me.TextBox3 '4 = colonne D Sous-Segments
Cells(ligne, 5) = Me.ComboBox2 '5 = colonne E Acheteur
Cells(ligne, 6) = Me.TextBox2 '6 = colonne F Expert
Cells(ligne, 7) = Me.TextBox4 '7 = colonne G Intitulé
Cells(ligne, 8) = Me.TextBox5 '8 = colonne H Description
Cells(ligne, 9) = Me.TextBox6 '9 = colonne I Stratégie
Cells(ligne, 10) = CDbl(Me.TextBox7) '10 = colonne J Montant Annuel
Cells(ligne, 11) = Me.ComboBox3 '11 = colonne K Renouvellement
Cells(ligne, 12) = CDate(Me.TextBox8) '12 = colonne L Echéance
Cells(ligne, 13) = CDate(Me.TextBox9) '13 = colonne M Date Lancement
Cells(ligne, 14) = Me.TextBox10 '14 = colonne N Durée de Réalisation
Cells(ligne, 15) = Me.ComboBox4 '15 = colonne O Etat
Cells(ligne, 16) = Me.ComboBox5 '16 = colonne P Stratégique
End With
End Sub
Private Sub CommandButton3_Click()
F_calendrier.Show
F_calendrier.Left = 600
F_calendrier.Top = 440
End Sub
Private Sub CommandButton4_Click()
F_calendrier2.Show
F_calendrier2.Left = 600
F_calendrier2.Top = 440
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If InStr("1234567890,-.€", Chr(KeyAscii)) = 0 Then KeyAscii = 0
' Si le textbox contient une virgule
If InStr(1, Me.TextBox7, ",") Then
' Si la longeur après la virgule et de 2
If Len(Mid(Me.TextBox7, InStr(1, Me.TextBox7, ","))) = 2 Then
' On affecte un format au textbox + le dernier code envoyé
Me.TextBox7.Value = Format(Me.TextBox7 & Chr(KeyAscii), "#,###.00 €")
KeyAscii = 0 ' empêcher le doublon
End If
End If
End Sub