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
| Private Sub CommandButtonok2_Click() 'affiche les valeurs dans excel
Dim Ligne As Long, C As Range, ResLigne As Long
With Sheets("Feuil2")
Set C = .[A:I].Find("*", , , , xlByRows, xlPrevious)
If C Is Nothing Then
Ligne = 5
Else
Ligne = C.Row + 3
End If
With .Cells(Ligne, 1).Resize(, 9).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
ResLigne = Ligne
End With
.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
Ligne = Ligne + 1
.Cells(Ligne, 7) = "TOTAL"
.Cells(Ligne, 8) = Tot
If Me.TextBoxcashamount.Text <> "" And IsNumeric(Me.TextBoxcashamount.Text) Then
If CDbl(Me.TextBoxcashamount.Text) <> Tot Then
Ligne = Ligne + 1
.Cells(Ligne, 7) = "CASH ADVANCE"
.Cells(Ligne, 8) = CDbl(Me.TextBoxcashamount.Text)
Ligne = Ligne + 1
.Cells(Ligne, 7) = "REMAINING"
.Cells(Ligne, 8) = Tot - CDbl(Me.TextBoxcashamount.Text)
End If
End If
With .Cells(Ligne, 1).Resize(, 9).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With .Range(.Cells(ResLigne, 9), .Cells(Ligne, 9)).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
UserFormBusinesstrip.Hide
Unload UserFormBusinesstrip
End With
End Sub |
