Private Sub placementRange()
Dim EcX&, EcY&, L1#, T1#
EcX = 4 '(Me.Width - Me.InsideWidth)
EcY = Me.Height - Me.InsideHeight
If ActiveWindow.Zoom > 100 Then EcX = EcX / (ActiveWindow.Zoom / 100)
L1 = ((ActiveWindow.ActivePane.PointsToScreenPixelsX(0) / PtoPx) + EcX) * (ActiveWindow.Zoom / 100) + ((Range(Obj, Cells(1, 1)).Width - Obj.Width) * (ActiveWindow.Zoom / 100))
T1 = ((ActiveWindow.ActivePane.PointsToScreenPixelsY(0) / PtoPx) + EcX) * (ActiveWindow.Zoom / 100) + (Range(Obj.Offset(IIf(Obj.Row > 1, -1, 0)), Cells(1, 1)).Height * (ActiveWindow.Zoom / 100))
'connaitre le nombre de ligne a ratraper en terme de top
With ActiveWindow
R = .ScrollRow - .SplitRow - 1
If .ScrollRow > .SplitRow + 1 And Obj.Row <= .SplitRow Then
Set lx = Range(Cells(.SplitRow + 1, 1), Cells(.VisibleRange.Row - 1, 1))
[A1] = "Rows " & lx.EntireRow.Address(0, 0) & " a ratraper) soit " & lx.Height & "points"
T1 = T1 + (lx.Height * (.Zoom / 100))
Else
[A1] = "0 ligne a ratraper"
End If
'connaitre le nombre de colonne a ratraper en terme de left
C = .ScrollColumn - .SplitColumn - 1
If .ScrollColumn > .SplitColumn + 1 And Obj.Column <= .SplitColumn Then
Set cy = Range(Cells(1, .SplitColumn + 1), Cells(1, .VisibleRange.Column - 1))
[A2] = "Columns " & cy.EntireColumn.Address(0, 0) & " a ratraper)soit " & cy.Width & "points"
L1 = L1 + (cy.Width * (.Zoom / 100))
Else
[A2] = "0 colonne a ratraper"
End If
If Obj.Row = 1 Then T1 = T1 - Obj.Height * (.Zoom / 100)
'a la base on est en top et left de obj
' donc ici tu ajoute la position autour de cette cellule
'pour la blague j'utilise des constante excel
'comme postop et posleft sont public et double elle valent 0 (si non renseigné dans l'apel dans le sheets) donc elle sont optional implicitement
Select Case posTop
Case xlBottom: T1 = T1 + Obj.Height * (.Zoom / 100)
Case xlCenter: T1 = T1 + (Obj.Height * (.Zoom / 100)) / 2
'etc..etc...
End Select
Select Case posLeft
Case xlRight: L1 = L1 + Obj.Width * (.Zoom / 100)
Case xlCenter: L1 = L1 + (Obj.Width * (.Zoom / 100)) / 2
Case -1: L1 = L1 - ((Me.Width - Obj.Width) / 2)
'etc..etc...
End Select
End With
'---------------------------------ICI CONTRAINTE ALTERNATIVE POUR NE PAS SORTIR DE LA FENÊTRE APPLICATION-----------------------------------
If L1 < Application.Left Then L1 = ((ActiveWindow.ActivePane.PointsToScreenPixelsX(0) / PtoPx) + EcX) * (ActiveWindow.Zoom / 100)
If L1 > (Abs(Application.Left) + Application.Width - Me.Width) Then L1 = (Application.Left + Application.Width) - Me.Width - EcY
If T1 > (Abs(Application.Top) + Application.Height - Me.Height) Then T1 = (Application.Top + Application.Height) - Me.Height - EcY
'application des coordonées
Me.Left = L1
Me.Top = T1
End Sub