Re : problème excel macro VBA Userform: Changer format texte en format nombre et %
merci jp
je pense que je me suis encore mal exprimée sur ce que je souhaite, je vous envoie les codes ci-joint:
J'aimerai convertir le taux de tva en nombre et enlever les decimales en trop...
Pouvez vous m'aider ?
je désespère...
Dim BdCli As Worksheet
Private Sub Bt_Effacer_Click()
'Supression de toutes les données du userform grâce à cette macro
UF_Listcli.Value = ""
UF_Chantier.Value = ""
UF_Com.Value = ""
UF_ListDAS.Value = ""
UF_Listtrav.Value = ""
UF_Nom.Value = ""
UF_Prénom.Value = ""
UF_Adresse.Value = ""
UF_Cp.Value = ""
UF_Ville.Value = ""
UF_Typecli.Value = ""
UF_MontTTC.Value = ""
UF_ListtxTVA.Value = ""
UF_MontTVA.Value = ""
UF_MontHT.Value = ""
UF_Date.Value = ""
UF_Mois.Value = ""
End Sub
Private Sub Bt_Valider_Click()
Dim nr As Integer
If Me.UF_Listcli = "" Or Me.UF_Chantier = "" Or Me.UF_Nom = "" Or Me.UF_Adresse = "" Or Me.UF_Cp = "" Or Me.UF_Ville = "" Or Me.UF_Typecli = "" Or Me.UF_Com = "" Or Me.UF_ListDAS = "" Or Me.UF_Listtrav = "" Or Me.UF_MontTTC = "" Or Me.UF_ListtxTVA = "" Or Me.UF_MontTVA = "" Or Me.UF_MontHT = "" Or Me.UF_Date = "" Or Me.UF_Mois = "" Then
MsgBox "Saisir les champs incomplets"
GoTo Ending
Else
nr = ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(Rows.Count, 1).End(xlUp).Row + 1
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 1) = UF_Chantier
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 2) = UF_Nom
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 3) = UF_Prénom
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 4) = UF_Adresse
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 5) = UF_Cp.Value
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 6) = UF_Ville
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 7) = UF_Typecli
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 8) = UF_Com
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 9) = UF_ListDAS
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 10) = UF_Listtrav
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 11) = UF_MontTTC.Value
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 12) = UF_ListtxTVA.Value
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 13) = UF_MontTVA.Value
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 14) = UF_MontHT.Value
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 15) = UF_Date.Value
ThisWorkbook.Sheets("Nouveaux Chantiers").Cells(nr, 16) = UF_Mois
Me.UF_Listcli = ""
Me.UF_Chantier = ""
Me.UF_Nom = ""
Me.UF_Prénom = ""
Me.UF_Adresse = ""
Me.UF_Cp = ""
Me.UF_Ville = ""
Me.UF_Typecli = ""
Me.UF_Com = ""
Me.UF_ListDAS = ""
Me.UF_Listtrav = ""
Me.UF_MontTTC = ""
Me.UF_ListtxTVA = ""
Me.UF_MontTVA = ""
Me.UF_MontHT = ""
Me.UF_Date = ""
Me.UF_Mois = ""
Me.UF_Listcli.SetFocus
Unload Me
End If
Ending:
End Sub
Private Sub UserForm_Initialize()
Set BdCli = Sheets("Base de données")
' Formule pour liste déroulante en cascade
Sheets("DAS").Activate
For i = 1 To [A65000].End(xlUp).Row
If Cells(i, 1) <> "" Then Me.UF_ListDAS.AddItem Cells(i, 1)
Next i
End Sub
Private Sub UF_Listcli_Change()
' Déclaration de la variable Vligne
Dim Vligne As Long
'Voir feuille 11
If Me.UF_Listcli.ListIndex = -1 Then
Exit Sub
Else
Vligne = Me.UF_Listcli.ListIndex + 2
' Permet de faire l'extraction des différentes données dans la Base Access en fontion des infos liste
UF_Nom = BdCli.Cells(Vligne, "F")
UF_Prénom = BdCli.Cells(Vligne, "G")
UF_Adresse = BdCli.Cells(Vligne, "I")
UF_Cp = BdCli.Cells(Vligne, "J")
UF_Ville = BdCli.Cells(Vligne, "K")
UF_Typecli = BdCli.Cells(Vligne, "B")
UF_Com = BdCli.Cells(Vligne, "Q")
End If
End Sub
Private Sub UF_MontTTC_Change()
' Formule calcul montant hors taxe 1
If UF_ListtxTVA.Value = "" Then Exit Sub
For i = 1 To Len(UF_MontTTC.Value)
UF_MontHT = UF_MontTTC / (1 + UF_ListtxTVA)
Next i
' Formule calcul Montant HT
If UF_MontHT.Value = "" Then Exit Sub
For i = 1 To Len(UF_MontTTC.Value)
UF_MontTVA = UF_MontTTC - UF_MontHT
Next i
End Sub
Private Sub UF_ListtxTVA_Change()
' Formule calcul montant hors taxe 2
If UF_MontTTC.Value = "" Then Exit Sub
For i = 1 To Len(UF_ListtxTVA.Value)
UF_MontHT = UF_MontTTC / (1 + UF_ListtxTVA.Value)
Next i
End Sub
Private Sub UF_MontHT_Change()
' Formule calcul Montant HT
If UF_MontTTC.Value = "" Then Exit Sub
For i = 1 To Len(UF_MontHT.Value)
UF_MontTVA = UF_MontTTC - UF_MontHT
Next i
End Sub
Private Sub UF_ListDAS_Change()
'Formule liste en cascade
Me.UF_Listtrav.Value = ""
derlig = Cells(65000, Me.UF_ListDAS.ListIndex + 3).End(xlUp).Row
Range(Cells(2, Me.UF_ListDAS.ListIndex + 3), Cells(derlig, Me.UF_ListDAS.ListIndex + 3)).Name = "List_DAS2"
Me.UF_Listtrav.RowSource = [List_DAS2].Address
End Sub
Private Sub UserForm_Terminate()
' Macro ouverture feuille paiement à la fin du user
Sheets("Nouveaux Chantiers").Activate
End Sub
Private Sub UF_Date_Change()
' macro pour format date
On Error Resume Next
UF_Mois = Format(CDate(UF_Date.Value), "mmmm")
End Sub
