Private Sub Worksheet_Activate()
DebloqueFeuilles
Sheets("CbBox").Visible = False
'Sheets("ClientsCoordonnées").Visible = True
Application.MoveAfterReturn = True
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayGridlines = False
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
Range("L18").Select
Rows("1:13").RowHeight = 0
Rows("15:19").RowHeight = 14
Rows("20:65").RowHeight = 12
[B19].Select
' mise à jour RdVs
If [m14] <> 1 Then
[E19] = "=IF(RC[-2]<>"""",ClientsCoordonnées!R[-15]C[-3],0)"
[F19] = "=IF(RC[-1]="""","""",IF(ClientsCoordonnées!R[-15]C[-2]<>"""",ClientsCoordonnées!R[-15]C[14],""""))"
[g19] = "=ClientsCoordonnées!R[-15]C[3]"
[H19] = "=ClientsCoordonnées!R[-15]C[10]"
[M19] = "=IF(RC[-8]=0,0,IF(RC[2]=""stop"",0,LOOKUP(RC[-8],RDVap)*4.35))"
[e19:m19].Copy
[e20:m64].Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
[e19:m64].Select
Selection.Copy
Range("e19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
[O19] = "=IF(RC[-10]<>0,LOOKUP(RC[-10],Stop),0)"
[O19].Copy
[o20:o64].Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
[o19:o64].Select
Selection.Copy
Range("o19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
[u19] = "=IF(RC5="""",0,SUMPRODUCT((MONTH(RendezVous!R4C12:R20000C12)&YEAR(RendezVous!R4C12:R20000C12)=MONTH(R17C)&YEAR(R17C))*((RendezVous!R4C6:R20000C6)=RC5)))"
[u19].Copy
Range("u20:u64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("u19:u64").Select
Selection.Copy
Range("u19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
[V19] = "=IF(RC[-9]<>0,RC[-1]/RC[-9],0)"
[W19] = "=IF(RC[-10]=0,0,RC[-10]-RC[-2])"
[v19:w19].Copy
Range("v20:w64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("v19:w64").Select
Selection.Copy
Range("v19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
' mise à jour modif Appels - Rappels - Prépa Mandat
[X19] = "=IF(RC[-16]<>RC[-17],RC[-16],"""")"
[X19].Copy
[x20:x64].Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
[x19:x64].Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
[y19] = "=IF(AND(RC[-11]<>RC[-10],RC[-10]=""OK""),""Prospection"",IF(AND(RC[-11]<>RC[-10],RC[-10]=""rappel""),""Rappels"",IF(AND(RC[-11]<>RC[-10],RC[-10]=""stop""),""NON"",IF(AND(RC[-11]<>RC[-10],RC[-10]=""attente""),""NON"",""""))))"
[y19].Copy
[y20:y64].Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
[y19:y64].Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'mise à jour mensuelle
If [m14] = 1 Then
[p19] = "=IF(RC5="""",0,SUMPRODUCT((MONTH(RendezVous!R4C12:R20000C12)&YEAR(RendezVous!R4C12:R20000C12)=MONTH(R17C)&YEAR(R17C))*((RendezVous!R4C6:R20000C6)=RC5)))"
[p19].Copy
[p19:u64].Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("p19:u64").Select
Selection.Copy
Range("p19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
ActiveSheet.Unprotect Password:="Krameri"
Range(Cells(1, 33), Cells(1, 1)).Select
ActiveWindow.Zoom = True
Range("b1:b64").Select
For Each cellule In Selection
If cellule.Value = "" Then cellule.EntireRow.Hidden = True
Next cellule
If [x14] <> 46 Then
ActiveSheet.Shapes("Afaire_effCom").Visible = True
ActiveSheet.Shapes("AFaire_speedy").Visible = False
Else
ActiveSheet.Shapes("Afaire_effCom").Visible = False
ActiveSheet.Shapes("AFaire_speedy").Visible = True
End If
Rows("65:65").RowHeight = 1
Range("e14").Select
'Sheets("ClientsCoordonnées").Visible = False
BloqueFeuilles
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect Password:="Krameri", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.LargeScroll Down:=-1
End Sub
Private Sub Worksheet_SelectionChange(ByVal r As Range)
ListBox1.Visible = False
If Not Intersect(ActiveCell, [ab19:ab64]) Is Nothing Then afficher "p72:p75"
If Not Intersect(ActiveCell, [ac19:ac64]) Is Nothing Then afficher "p79:p82"
If Not Intersect(ActiveCell, [ad19:ad64]) Is Nothing Then afficher "v72:v76"
If Not Intersect(ActiveCell, [ae19:ae64]) Is Nothing Then afficher "v78:v82"
If Not Intersect(r, Range("f19:f64")) Is Nothing Then
Application.EnableEvents = False
ActiveCell.Offset(0, 1).Resize(1, 27).Select
Application.EnableEvents = True
End If
End Sub