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
|
' ouverture fichier
Private Sub UserForm_Initialize()
' activez les sources de menu déroulant
Sheets("tarif").Select
'Range("d1:g9").Select
'Selection.ClearContents
Range("d1:g9").ClearContents
c12ref1.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref2.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref3.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref4.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref5.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref6.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref7.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref8.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
c12ref9.RowSource = "tarif!a1:n" & Range("tarif!a65536").End(xlUp).Row
End Sub
'saisie du nom
Private Sub boxnom_AfterUpdate()
'Me.boxnom.Value.Copy Sheets("recap commande").Range("a65536").End(xlUp).Offset(1, 0)
'Sheets("tarif").Range("d1").Copy Sheets("recap commande").Range("a65536").End(xlUp).Offset(1, 0)
Sheets("recap commande").Select
Range("a65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = boxnom.Value
End Sub
'EVENEMENT POUR CHAQUE SELECTION DE REF
Private Sub c12ref1_Change()
Sheets("tarif").Select
Dim rech1 As String
Dim plage As Range, c As Range
rech1 = c12ref1.Value
Set plage = Worksheets("tarif").UsedRange
With plage
Set c = .Find(What:=rech1, LookAt:=xlPart)
If Not c Is Nothing Then
Range("f1").Value = c.Offset(0, 1).Value
c12p1.Value = c.Offset(0, 1).Value
Range("e1").Value = rech1
End If
End With
End Sub
' evenement sur changement quantité
Private Sub q1_Change()
Dim total1 As Double
Range("g1").Value = Val(q1)
t1.Value = Range("h1").Value
End Sub
'vérification du total
Private Sub CommandButton3_Click()
totalg = Range("h10").Value
Sheets("recap commande").Select
Range("a65536").End(xlUp).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Sheets("tarif").Range("h10").Value
End Sub
' validation
Private Sub CommandButton1_Click()
Dim numdl As String, i As Integer
Sheets("tarif").Select
numdl = Range("e65536").End(xlUp).Row
For i = 1 To numdl
Range("d" & i).Value = boxnom.Value
Next i
Unload UserForm1
End Sub
'annulation
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub |