MuscatMimi
XLDnaute Accro
Bonsoir a tous
J'ai ce code dans un Module
Public Sub Calcul(Lgn As Integer)
Dim Cel As Range
Worksheets("INTERVENTIONS").Activate
With ActiveSheet
For Each Cel In .Range("I2:I" & Lgn)
If Cel.Row = 2 Then
If Cel.Offset(0, -2) <> "" Then Cel = .Range("L1") - Cel.Offset(0, -2) 'débit
If Cel.Offset(0, -1) <> "" Then Cel = .Range("L1") + Cel.Offset(0, -1) 'crédit
Else
If Cel.Offset(0, -2) <> "" Then Cel = Cel.Offset(-1, 0) - Cel.Offset(0, -2) 'débit
If Cel.Offset(0, -1) <> "" Then Cel = Cel.Offset(-1, 0) + Cel.Offset(0, -1) 'crédit
End If
Next Cel
.Range("G" & Lgn & ":I" & Lgn).NumberFormat = "# ##0.00 €"
.Range("L2") = .Range("L1") + Evaluate("SUM(H2:H" & Lgn & ")")
.Range("L3") = Evaluate("SUM(G2:G" & Lgn & ")")
End With
End Sub
Comment faire pour que ce calcul s'éxécute dans le code "Validation" de mon Usf "ConsultationEcritures" noté ci-dessous
Private Sub CommandButton2_Click()'Validation de Modification
Dim k As Byte, ItemSelect As Long, x As Byte, Numlign As Long
With ListView1
ItemSelect = .SelectedItem.Index 'N° de l'index sélectionné dans la lisview
Numlign = Right(.ListItems(.SelectedItem.Index).Key, Len(.ListItems(.SelectedItem.Index).Key) - 1) 'N° ligne de la feuille
If MsgBox("Confirmation de la modification.", vbYesNo, "Confirmation") = vbYes Then
'mise à jour des colonnes de la listview
.ListItems(ItemSelect).Text = UCase(TextBox1)
For k = 1 To 8
If k <> 2 Then .ListItems(ItemSelect).ListSubItems(k).Text = Controls("TextBox" & k + 1)
Next
.ListItems(ItemSelect).ListSubItems(2).Text = CDate(TextBox3)
'mise à jour de la feuille
Sheets("INTERVENTIONS").Cells(Numlign, 1) = UCase(.ListItems(ItemSelect).Text)
Sheets("INTERVENTIONS").Cells(Numlign, 2) = .ListItems(ItemSelect).ListSubItems(1).Text
Sheets("INTERVENTIONS").Cells(Numlign, 3) = CDate(.ListItems(ItemSelect).ListSubItems(2).Text)
For k = 3 To 5
Sheets("INTERVENTIONS").Cells(Numlign, k + 1) = .ListItems(ItemSelect).ListSubItems(k).Text
Next
If .ListItems(ItemSelect).ListSubItems(6).Text <> "" Then _
Sheets("INTERVENTIONS").Cells(Numlign, 7) = CDbl(.ListItems(ItemSelect).ListSubItems(6).Text)
Sheets("INTERVENTIONS").Cells(Numlign, 8) = .ListItems(ItemSelect).ListSubItems(7).Text
Sheets("INTERVENTIONS").Cells(Numlign, 9) = .ListItems(ItemSelect).ListSubItems(8).Text
MiseEnForme
.ListItems(ItemSelect).Selected = False
For x = 1 To 9
Controls("TextBox" & x) = ""
Next
CommandButton2.Enabled = False
Alim_Combo
Vide_Combo
Exit Sub
Else
.ListItems(ItemSelect).Selected = False
For x = 1 To 9
Controls("TextBox" & x) = ""
Next
CommandButton2.Enabled = False
Exit Sub
End If
End With
End Sub
Merci a l'avance
J'ai ce code dans un Module
Public Sub Calcul(Lgn As Integer)
Dim Cel As Range
Worksheets("INTERVENTIONS").Activate
With ActiveSheet
For Each Cel In .Range("I2:I" & Lgn)
If Cel.Row = 2 Then
If Cel.Offset(0, -2) <> "" Then Cel = .Range("L1") - Cel.Offset(0, -2) 'débit
If Cel.Offset(0, -1) <> "" Then Cel = .Range("L1") + Cel.Offset(0, -1) 'crédit
Else
If Cel.Offset(0, -2) <> "" Then Cel = Cel.Offset(-1, 0) - Cel.Offset(0, -2) 'débit
If Cel.Offset(0, -1) <> "" Then Cel = Cel.Offset(-1, 0) + Cel.Offset(0, -1) 'crédit
End If
Next Cel
.Range("G" & Lgn & ":I" & Lgn).NumberFormat = "# ##0.00 €"
.Range("L2") = .Range("L1") + Evaluate("SUM(H2:H" & Lgn & ")")
.Range("L3") = Evaluate("SUM(G2:G" & Lgn & ")")
End With
End Sub
Comment faire pour que ce calcul s'éxécute dans le code "Validation" de mon Usf "ConsultationEcritures" noté ci-dessous
Private Sub CommandButton2_Click()'Validation de Modification
Dim k As Byte, ItemSelect As Long, x As Byte, Numlign As Long
With ListView1
ItemSelect = .SelectedItem.Index 'N° de l'index sélectionné dans la lisview
Numlign = Right(.ListItems(.SelectedItem.Index).Key, Len(.ListItems(.SelectedItem.Index).Key) - 1) 'N° ligne de la feuille
If MsgBox("Confirmation de la modification.", vbYesNo, "Confirmation") = vbYes Then
'mise à jour des colonnes de la listview
.ListItems(ItemSelect).Text = UCase(TextBox1)
For k = 1 To 8
If k <> 2 Then .ListItems(ItemSelect).ListSubItems(k).Text = Controls("TextBox" & k + 1)
Next
.ListItems(ItemSelect).ListSubItems(2).Text = CDate(TextBox3)
'mise à jour de la feuille
Sheets("INTERVENTIONS").Cells(Numlign, 1) = UCase(.ListItems(ItemSelect).Text)
Sheets("INTERVENTIONS").Cells(Numlign, 2) = .ListItems(ItemSelect).ListSubItems(1).Text
Sheets("INTERVENTIONS").Cells(Numlign, 3) = CDate(.ListItems(ItemSelect).ListSubItems(2).Text)
For k = 3 To 5
Sheets("INTERVENTIONS").Cells(Numlign, k + 1) = .ListItems(ItemSelect).ListSubItems(k).Text
Next
If .ListItems(ItemSelect).ListSubItems(6).Text <> "" Then _
Sheets("INTERVENTIONS").Cells(Numlign, 7) = CDbl(.ListItems(ItemSelect).ListSubItems(6).Text)
Sheets("INTERVENTIONS").Cells(Numlign, 8) = .ListItems(ItemSelect).ListSubItems(7).Text
Sheets("INTERVENTIONS").Cells(Numlign, 9) = .ListItems(ItemSelect).ListSubItems(8).Text
MiseEnForme
.ListItems(ItemSelect).Selected = False
For x = 1 To 9
Controls("TextBox" & x) = ""
Next
CommandButton2.Enabled = False
Alim_Combo
Vide_Combo
Exit Sub
Else
.ListItems(ItemSelect).Selected = False
For x = 1 To 9
Controls("TextBox" & x) = ""
Next
CommandButton2.Enabled = False
Exit Sub
End If
End With
End Sub
Merci a l'avance