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
| Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim lNo As Long
If Target.Column = 2 And Target.Row >= 13 And Target.Row <= 32 Then
If VarType(Target) <> vbEmpty Then
RechRefTarif Target.Text, Target.Row
Else
lNo = Target.Row
With ActiveSheet
.Unprotect "vp-CV"
.Range("A" & lNo) = ""
.Range("C" & lNo) = ""
.Range("G" & lNo) = ""
.Range("H" & lNo) = ""
.Range("K" & lNo) = ""
.Range("L" & lNo) = ""
.Range("M" & lNo) = ""
.Range("N" & lNo) = ""
.Range("P" & lNo) = ""
.Range("Q" & lNo) = ""
.Protect "vp-CV"
End With
End If
End If
End Sub
Private Sub RechRefTarif(sRef As String, lNo As Long)
Dim flle As Worksheet
Dim iNbProd As Integer, iNb As Integer
Dim rCel As Range
Dim lProd As Long
Load frmChoix
frmChoix.Tag = lNo
On Error GoTo GestionErreur
For Each flle In Workbooks("tarifs beta.xlsx").Worksheets
iNbProd = Application.WorksheetFunction.CountIf(flle.Columns("A:A"), sRef)
If iNbProd <> 0 Then
Set rCel = flle.Columns("A:A").Find(What:=sRef, After:=flle.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If iNbProd >= 1 Then
For iNb = 1 To iNbProd
With frmChoix.lstProd
.AddItem sRef
.Column(1, .ListCount - 1) = flle.Range("B" & rCel.Row)
.Column(2, .ListCount - 1) = flle.Range("M" & rCel.Row)
.Column(3, .ListCount - 1) = flle.Range("K" & rCel.Row)
.Column(4, .ListCount - 1) = flle.Name
.Column(5, .ListCount - 1) = rCel.Row
End With
Set rCel = flle.Columns("A:A").FindNext(After:=rCel)
Next
End If
End If
Next
Select Case frmChoix.lstProd.ListCount
Case 0
MsgBox "Cette référence n'est pas valide !", vbOKOnly + vbCritical, "ERREUR DE SAISIE"
Unload frmChoix
Range("B" & lNo).Select
Case 1
Set flle = Workbooks("tarifs beta.xlsx").Worksheets(frmChoix.lstProd.Column(4, 0))
lProd = frmChoix.lstProd.Column(5, 0)
With ActiveSheet
.Unprotect "vp-CV"
.Range("A" & lNo) = flle.Range("B" & lProd)
.Range("C" & lNo) = flle.Range("C" & lProd)
.Range("S" & lNo) = flle.Range("F" & lProd)
.Protect "vp-CV"
End With
Unload frmChoix
Case Else
frmChoix.Show
End Select
Exit Sub
GestionErreur:
If Err.Number = 9 Then
MsgBox "Veuillez ouvrir le classeur des tarifs", vbOKOnly + vbCritical, "ERREUR"
Else
MsgBox Err.Description, vbOKOnly + vbCritical, "ERREUR N° " & Err.Number
End If
End Sub
Private Sub RazBL()
ActiveSheet.Unprotect "vp-CV"
Range("A13:H32").ClearContents
Range("K13:R32").ClearContents
Range("K3").ClearContents
ActiveSheet.Protect "vp-CV"
End Sub |
Partager