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
| Sub Update_Historical_Demands()
Dim wb As Workbook
Dim ws1 As Worksheet
Dim lastRow As Long
Dim ctrl As Control, ctr As Control
Dim fr1 As Boolean, fr2 As Boolean, fr3 As Boolean, fr4 As Boolean, fr5 As Boolean, fr6 As Boolean, fr7 As Boolean, fr8 As Boolean
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("RecapHistoDemandeRef")
lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is msforms.checkbox Then
Select Case ctrl.Parent.name
Case "FrameBP2Axa"
If ctrl.Value = True Then fr4 = True
Case "FrameBP2Allianz"
If ctrl.Value = True Then fr5 = True
End Select
End If
Next ctrl
If fr4 = True Then
ws1.Cells(lastRow, 1) = UserForm1.TbxISIN.Value
ws1.Cells(lastRow, 2) = UserForm1.TbxNom.Value
ws1.Cells(lastRow, 3) = UserForm1.TbxDevise.Value
ws1.Cells(lastRow, 4) = UserForm1.TbxSdG.Value
ws1.Cells(lastRow, 5) = ws1.Cells(1, 9)
ws1.Cells(lastRow, 6) = "Axa"
ws1.Cells(lastRow, 7) = "nomcontrat1"
Else
If fr5 = True Then
ws1.Cells(lastRow, 1) = UserForm1.TbxISIN.Value
ws1.Cells(lastRow, 2) = UserForm1.TbxNom.Value
ws1.Cells(lastRow, 3) = UserForm1.TbxDevise.Value
ws1.Cells(lastRow, 4) = UserForm1.TbxSdG.Value
ws1.Cells(lastRow, 5) = ws1.Cells(1, 9)
ws1.Cells(lastRow, 6) = "Allianz"
ws1.Cells(lastRow, 7) = "nomcontrat2"
End If
End If
end sub |
Partager