Option Explicit
Private RngDon As Range, WshDevis As Worksheet, LCouDon As Long, TVLDon()
Private Sub UserForm_Initialize()
Set RngDon = Feuil1.ListObjects(1).DataBodyRange
cb_CIT.List = RngDon.Columns(1).Value
With Workbooks.Open(ThisWorkbook.Path & "\devis.xlsx") 'ouvre le fichier
Set WshDevis = .Worksheets(1)
End With
End Sub
Private Sub cb_CIT_Change()
If cb_CIT.MatchFound Then
LCouDon = cb_CIT.ListIndex + 1
TVLDon = RngDon.Rows(LCouDon).Value
Else
LCouDon = 0
ReDim TVLDon(1 To 1, 1 To 8)
End If
tb_unite = TVLDon(1, 6)
End Sub
Private Sub tb_Qte_Change()
tb_Prix = CDbl(tb_Qte.Text) * TVLDon(1, 8)
End Sub
Private Sub CommandButton1_Click()
Dim TVLDev(1 To 1, 1 To 5)
TVLDev(1, 1) = TVLDon(1, 1)
TVLDev(1, 2) = CDbl(tb_Qte.Text)
TVLDev(1, 3) = TVLDon(1, 6)
TVLDev(1, 5) = CCur(tb_Prix.Text)
TVLDev(1, 4) = TVLDev(1, 5) / TVLDev(1, 2)
With WshDevis.[A1].CurrentRegion
.Rows(.Rows.Count + 1).Value = TVLDev
End With
End Sub
Private Sub CommandButton2_Click()
cb_CIT = ""
tb_Qte = ""
tb_Prix = ""
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
WshDevis.Parent.Close SaveChanges:=True
End Sub