Option Explicit
Public obj As Range
Public IndexPane&
Private Sub UserForm_Activate()
placementRange obj
End Sub
Private Function placementRange(obj As Object)
If obj Is Nothing Then Exit Function
' collection Fonctions avec PointsToScrenPixels(X Y) / Activewindow / Activepane / panes(1 to 4) / visiblerange etc...
' récupérer la distance (des bords de l’écran a la cellule désignée)en points théoriques en incluant le freezepane et figés et c....
' version 2.0
' date février 2022
' auteur :patricktoulon
' Code simplifié
Dim PtsToPxX#, PtsToPxy#, TheZoom#, PaN As Pane, Eq As Boolean, Addr$, ip&, L1, T1, I&
With ActiveWindow
Eq = IndexPane > 0: Addr = obj.Address(0, 0): ip = IndexPane
If IndexPane > .Panes.Count Or IndexPane = 0 Then Set PaN = .ActivePane: IndexPane = .ActivePane.Index Else: Set PaN = .Panes(IndexPane)
If .FreezePanes = True Then
For I = 1 To .Panes.Count
If Not Intersect(obj, .Panes(I).VisibleRange) Is Nothing Then Set PaN = .Panes(I) '.Index:
Next
End If
If Eq = True And Intersect(obj, .Panes(IndexPane).VisibleRange) Is Nothing Then
L1 = 0: T1 = 0
MsgBox Addr & " n'est pas VISIBLE!!! dans la pane " & ip: Exit Function
Else
PtsToPxX = ((.Panes(1).PointsToScreenPixelsX(72) - .Panes(1).PointsToScreenPixelsX(0)) / 72) 'défini le coeff point to pixel horizontal
PtsToPxX = Array((4 / 3), (4 / 3) * 1.25)(Abs(PtsToPxX > 1.4))
TheZoom = .Zoom / 100 'défini le coeff zoom
L1 = ((PaN.PointsToScreenPixelsX(Int(obj.Left)) / PtsToPxX) * TheZoom) 'left en point
T1 = ((PaN.PointsToScreenPixelsY(Int(obj.Top)) / PtsToPxX) * TheZoom) - IIf(Not .FreezePanes, 1, 0) 'top en point
End If
End With
L1 = L1 '+4'pour 2007 ou window 7 avec version inf à 2016
T1 = T1 '+4'pour 2007 ou window 7 avec version inf à 2016
If L1 > Application.Left + Application.Width - Me.Width Then L1 = Application.Left + Application.Width - Me.Width - 15
If T1 > Application.Top + Application.Height - Me.Height Then T1 = Application.Top + Application.Height - Me.Height - 15
With Me: .Left = L1: .Top = T1: End With
End Function