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
| Private Sub UserForm_Initialize() ' c'est bien userform qui doit etre ecrit
Tot = 0 ' Pour les sommes de la viewlist et insertion des lignes
Ctr = 0
ComboBoxCurrencypayment.RowSource = ("CURRENCY") 'definit les données pr la currency
ComboBoxCashcur.RowSource = ("CURRENCY")
CheckBoxCashrec = False
FrameCashreceive.Visible = False
CheckBoxManualledger = False
TextBoxManualledger.Visible = False
CheckBoxOrlcy = False
FrameLcy.Visible = False
LabelCash.Visible = False
TextBoxCash.Visible = False
LabelRemaining.Visible = False
TextBoxRemaining.Visible = False
With ListViewExpense
'Définit le nombre de colonnes et Entêtes
With .ColumnHeaders
'Ajoute colonnes en spécifiant le nom de l'entête
'et la largeur des colonnes
.Add , , "Receipt Date", 58
.Add , , "GL Code", 55
.Add , , "GL Code Description", 110
.Add , , "Project Code", 58
.Add , , "Budget Line", 58
.Add , , "LCY Amount", 70
.Add , , "X-Rate", 45
.Add , , "Amount", 70
.Add , , "Comments", 190
End With
End With
'Spécifie l'affichage en mode "Détails"
ListViewExpense.View = lvwReport
End Sub
Private Sub CheckBoxCashrec_Click()
Dim TextBoxcashamount As Double
Dim TextBoxRemaining As DoubleFrameCashreceive.Visible = CheckBoxCashrec = True
LabelCash.Visible = CheckBoxCashrec = True
TextBoxCash.Visible = CheckBoxCashrec = True
LabelRemaining.Visible = CheckBoxCashrec = True
TextBoxRemaining.Visible = CheckBoxCashrec = True
TextBoxcashamount = CDbl(TextBoxAmount)
TextBoxRemaining = TextBoxcashamount.Value - TextBoxTotalexp.ValueEnd Sub
Private Sub CommandButtonAddexp_Click() 'Remplissage de listview avec les expenses
With ListViewExpense
With .ListItems 'premiere ligne
.Add , , TextBoxReceiptdate.Value
End With
Ctr = Ctr + 1
With .ListItems(Ctr)
.ListSubItems.Add , , ComboBoxGl.Value
.ListSubItems.Add , , TextBoxGldesc.Value
.ListSubItems.Add , , TextBoxProjectcode.Value
.ListSubItems.Add , , TextBoxBudget.Value
.ListSubItems.Add , , TextBoxLcy.Value
.ListSubItems.Add , , TextBoxexr.Value
.ListSubItems.Add , , TextBoxAmount.Value
.ListSubItems.Add , , TextBoxComments.Value
Tot = Tot + CDbl(TextBoxAmount)
TextBoxTotalexp = Format(Tot, "#0.00")
End With
End With ' nettoie les cases pour nouvelles saisies
TextBoxReceiptdate.Value = Clear
TextBoxGldesc.Value = Clear
TextBoxProjectcode.Value = Clear
TextBoxBudget.Value = Clear
TextBoxLcy.Value = Clear
TextBoxexr.Value = Clear
TextBoxAmount.Value = Clear
TextBoxComments.Value = Clear
ComboBoxGl.Value = Clear
End Sub
Private Sub CheckBoxManualledger_Click()
TextBoxManualledger.Visible = CheckBoxManualledger = True
End Sub
Private Sub CheckBoxOrlcy_Click()
FrameLcy.Visible = CheckBoxOrlcy = True
End Sub
Private Sub CommandButtonok2_Click() 'affiche les valeurs dans excel
Dim Ligne As Long, C As Range
With Sheets("Feuil1")
Set C = .[G:G].Find("REMAINING", , , xlWhole, xlByRows, xlPrevious)
If C Is Nothing Then
Ligne = 5
Else
Ligne = C.Row + 3
End If
.Cells(Ligne, 1) = "Destination"
.Cells(Ligne, 2) = TextBoxDestination.Value
Ligne = Ligne + 1
.Cells(Ligne, 1) = "Business purpose"
.Cells(Ligne, 2) = TextBoxBusinessporpose.Value
Ligne = Ligne + 2
.Cells(Ligne, 1) = "Receipt Date"
.Cells(Ligne, 2) = "General Ledger Code"
.Cells(Ligne, 3) = "General Ledger Description"
.Cells(Ligne, 4) = "Project Code"
.Cells(Ligne, 5) = "Budget Line"
.Cells(Ligne, 6) = "Local Currency Amount"
.Cells(Ligne, 7) = "Exchange Rate"
.Cells(Ligne, 8) = "Amount"
.Cells(Ligne, 9) = "Comments"
Dim i As Integer, j As Integer
'Boucle sur toutes les lignes
For i = 1 To ListViewExpense.ListItems.Count
Ligne = Ligne + 1
Cells(Ligne, 1) = ListViewExpense.ListItems(i).Text
'Boucle sur les colonnes
For j = 1 To ListViewExpense.ColumnHeaders.Count - 1
Cells(Ligne, j + 1) = ListViewExpense.ListItems(i).ListSubItems(j).Text
Next j
Next i
Range("H65536").End(xlUp).Offset(1, 0) = TextBoxTotalexp.Value
If CheckBoxCashrec = True Then Range("H65536").End(xlUp).Offset(1, 0) = TextBoxCash.Value
If CheckBoxCashrec = True Then Range("H65536").End(xlUp).Offset(1, 0) = TextBoxRemaining.Value UserFormBusinesstrip.Hide
Unload UserFormBusinesstrip
End With
End Sub |
Partager