UserForm positioning to second row
 

I am using Excel in Office 2000. I need to show a userform that is
positioned with it's top aligned with the top of the second visible row of
the active worksheet. It also needs to have it's left side aligned with the
left side of the second visible column.

It seems userforms use screen offsets, and Excel rows and columns use the
Inner offset of the worksheet. How do I translate the row and column
coordinates to screen offsets? If the user has Excel not maximized, for
example, or if there are custom menu bars which scoot the top of the
activesheet down, then it becomes difficult to know what the UserForm
coordinates need to be.

D Zook

UserForm positioning to second row
 

Hi D..
Try following

You'll need the APIs as VBA thinks all monitors are 72dpi.
Now it even works on my dual monitor (vertical) setup.


'Insert this code to your form:
Option Explicit

Private Declare Function GetDC Lib "user32.dll" ( _
ByVal hwnd&) As Long
Private Declare Function ReleaseDC Lib "user32.dll" ( _
ByVal hwnd&, ByVal hDC&) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hDC&, ByVal nIndex&) As Long
Function ScreenRes&(iDir%)
Dim lDC&
Static res
If Not IsArray(res) Then
ReDim res(1) As Long
lDC = GetDC(0)
res(0) = GetDeviceCaps(lDC, 88&)
res(1) = GetDeviceCaps(lDC, 90&)
lDC = ReleaseDC(0, lDC)
End If
ScreenRes = res(iDir)
End Function


Private Sub UserForm_Activate()
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
1).Left) * 72 / ScreenRes(1)
End With

End Sub








--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


z wrote :

I am using Excel in Office 2000. I need to show a userform that is
positioned with it's top aligned with the top of the second visible
row of the active worksheet. It also needs to have it's left side
aligned with the left side of the second visible column.

It seems userforms use screen offsets, and Excel rows and columns use
the Inner offset of the worksheet. How do I translate the row and
column coordinates to screen offsets? If the user has Excel not
maximized, for example, or if there are custom menu bars which scoot
the top of the activesheet down, then it becomes difficult to know
what the UserForm coordinates need to be.

D Zook

UserForm positioning to second row
 

Hi keepITcool,
For me, it misses the preliminary conversion of the co-ordinates of range in
pixels.

Sub AlignToRange()
'Const PixelToPoint = 3 / 4
'Const PointToPixel = 4 / 3
Dim Rng As Range
Set Rng = Range("B2")
With UserForm1
..StartUpPosition = 0
..Left = ActiveWindow.PointsToScreenPixelsX _
(Rng.Left * 4 / 3) * 3 / 4
..Top = ActiveWindow.PointsToScreenPixelsY _
(Rng.Top * 4 / 3) * 3 / 4
..Show
End With
End Sub

MP

"keepITcool" a écrit dans le message de
ft.com...

Hi D..
Try following

You'll need the APIs as VBA thinks all monitors are 72dpi.
Now it even works on my dual monitor (vertical) setup.


'Insert this code to your form:
Option Explicit

Private Declare Function GetDC Lib "user32.dll" ( _
ByVal hwnd&) As Long
Private Declare Function ReleaseDC Lib "user32.dll" ( _
ByVal hwnd&, ByVal hDC&) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hDC&, ByVal nIndex&) As Long
Function ScreenRes&(iDir%)
Dim lDC&
Static res
If Not IsArray(res) Then
ReDim res(1) As Long
lDC = GetDC(0)
res(0) = GetDeviceCaps(lDC, 88&)
res(1) = GetDeviceCaps(lDC, 90&)
lDC = ReleaseDC(0, lDC)
End If
ScreenRes = res(iDir)
End Function


Private Sub UserForm_Activate()
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
1).Left) * 72 / ScreenRes(1)
End With

End Sub








--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


z wrote :

I am using Excel in Office 2000. I need to show a userform that is
positioned with it's top aligned with the top of the second visible
row of the active worksheet. It also needs to have it's left side
aligned with the left side of the second visible column.

It seems userforms use screen offsets, and Excel rows and columns use
the Inner offset of the worksheet. How do I translate the row and
column coordinates to screen offsets? If the user has Excel not
maximized, for example, or if there are custom menu bars which scoot
the top of the activesheet down, then it becomes difficult to know
what the UserForm coordinates need to be.

D Zook

UserForm positioning to second row
 

Michel,

Good call!

You are correct. I forgot to adjust the Top/Left props that I read.
(which I didnt see as I position near the top/left corner.. so the
converted numbers are small)

I notice you use a 'hardcoded' 3/4 4/3 conversion (equivalent to 72/96
and 96/72). I assume it's for explanation only.

I'll stick to using the actual screenres iso assuming all users are set
to the default 96dpi.


I've amended my demo as follows.

Private Sub UserForm_Activate()
'position under active cell
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(ActiveCell(2, _
1).Top * ScreenRes(0) / 72) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(ActiveCell(1, _
2).Left * ScreenRes(1) / 72) * 72 / ScreenRes(1)
End With
Repaint
Application.Wait (Now + TimeSerial(0, 0, 2))
'position on row2,col2 of visible range
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top * ScreenRes(0) / 72) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
2).Left * ScreenRes(1) / 72) * 72 / ScreenRes(1)
End With

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Michel Pierron wrote :

Hi keepITcool,
For me, it misses the preliminary conversion of the co-ordinates of
range in pixels.

Sub AlignToRange()
'Const PixelToPoint = 3 / 4
'Const PointToPixel = 4 / 3
Dim Rng As Range
Set Rng = Range("B2")
With UserForm1
.StartUpPosition = 0
.Left = ActiveWindow.PointsToScreenPixelsX _
(Rng.Left * 4 / 3) * 3 / 4
.Top = ActiveWindow.PointsToScreenPixelsY _
(Rng.Top * 4 / 3) * 3 / 4
.Show
End With
End Sub

MP

"keepITcool" a écrit dans le message de
ft.com...

Hi D..
Try following

You'll need the APIs as VBA thinks all monitors are 72dpi.
Now it even works on my dual monitor (vertical) setup.


'Insert this code to your form:
Option Explicit

Private Declare Function GetDC Lib "user32.dll" ( _
ByVal hwnd&) As Long
Private Declare Function ReleaseDC Lib "user32.dll" ( _
ByVal hwnd&, ByVal hDC&) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hDC&, ByVal nIndex&) As Long
Function ScreenRes&(iDir%)
Dim lDC&
Static res
If Not IsArray(res) Then
ReDim res(1) As Long
lDC = GetDC(0)
res(0) = GetDeviceCaps(lDC, 88&)
res(1) = GetDeviceCaps(lDC, 90&)
lDC = ReleaseDC(0, lDC)
End If
ScreenRes = res(iDir)
End Function


Private Sub UserForm_Activate()
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
1).Left) * 72 / ScreenRes(1)
End With

End Sub








--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


z wrote :

I am using Excel in Office 2000. I need to show a userform that
is positioned with it's top aligned with the top of the second
visible row of the active worksheet. It also needs to have it's
left side aligned with the left side of the second visible column.

It seems userforms use screen offsets, and Excel rows and columns
use the Inner offset of the worksheet. How do I translate the
row and column coordinates to screen offsets? If the user has
Excel not maximized, for example, or if there are custom menu
bars which scoot the top of the activesheet down, then it becomes
difficult to know what the UserForm coordinates need to be.

D Zook

UserForm positioning to second row
 

Re keepITcool,

Except for the menu bars, Excel uses points while the API functions uses
pixels.
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Sub Ratio()
Dim hwnd&, R As RECT, msg As String
hwnd = FindWindow(vbNullString, Application.Caption)
GetWindowRect hwnd, R
MsgBox "Points/Pixels Ratio: " _
& Application.Width / (R.Right - R.Left) _
& vbTab & "- (3/4)" & vbLf & "Pixels" _
& "/Points Ratio: " & Format((R.Right - R.Left) _
/ Application.Width, "0.00") & vbTab & "- (4/3)", 64
End Sub

Regards,
MP

"keepITcool" a écrit dans le message de
.com...
Michel,

Good call!

You are correct. I forgot to adjust the Top/Left props that I read.
(which I didnt see as I position near the top/left corner.. so the
converted numbers are small)

I notice you use a 'hardcoded' 3/4 4/3 conversion (equivalent to 72/96
and 96/72). I assume it's for explanation only.

I'll stick to using the actual screenres iso assuming all users are set
to the default 96dpi.


I've amended my demo as follows.

Private Sub UserForm_Activate()
'position under active cell
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(ActiveCell(2, _
1).Top * ScreenRes(0) / 72) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(ActiveCell(1, _
2).Left * ScreenRes(1) / 72) * 72 / ScreenRes(1)
End With
Repaint
Application.Wait (Now + TimeSerial(0, 0, 2))
'position on row2,col2 of visible range
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top * ScreenRes(0) / 72) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
2).Left * ScreenRes(1) / 72) * 72 / ScreenRes(1)
End With

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Michel Pierron wrote :

Hi keepITcool,
For me, it misses the preliminary conversion of the co-ordinates of
range in pixels.

Sub AlignToRange()
'Const PixelToPoint = 3 / 4
'Const PointToPixel = 4 / 3
Dim Rng As Range
Set Rng = Range("B2")
With UserForm1
.StartUpPosition = 0
.Left = ActiveWindow.PointsToScreenPixelsX _
(Rng.Left * 4 / 3) * 3 / 4
.Top = ActiveWindow.PointsToScreenPixelsY _
(Rng.Top * 4 / 3) * 3 / 4
.Show
End With
End Sub

MP

"keepITcool" a écrit dans le message de
ft.com...

Hi D..
Try following

You'll need the APIs as VBA thinks all monitors are 72dpi.
Now it even works on my dual monitor (vertical) setup.


'Insert this code to your form:
Option Explicit

Private Declare Function GetDC Lib "user32.dll" ( _
ByVal hwnd&) As Long
Private Declare Function ReleaseDC Lib "user32.dll" ( _
ByVal hwnd&, ByVal hDC&) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hDC&, ByVal nIndex&) As Long
Function ScreenRes&(iDir%)
Dim lDC&
Static res
If Not IsArray(res) Then
ReDim res(1) As Long
lDC = GetDC(0)
res(0) = GetDeviceCaps(lDC, 88&)
res(1) = GetDeviceCaps(lDC, 90&)
lDC = ReleaseDC(0, lDC)
End If
ScreenRes = res(iDir)
End Function


Private Sub UserForm_Activate()
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
1).Left) * 72 / ScreenRes(1)
End With

End Sub








--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


z wrote :

I am using Excel in Office 2000. I need to show a userform that
is positioned with it's top aligned with the top of the second
visible row of the active worksheet. It also needs to have it's
left side aligned with the left side of the second visible column.

It seems userforms use screen offsets, and Excel rows and columns
use the Inner offset of the worksheet. How do I translate the
row and column coordinates to screen offsets? If the user has
Excel not maximized, for example, or if there are custom menu
bars which scoot the top of the activesheet down, then it becomes
difficult to know what the UserForm coordinates need to be.

D Zook

UserForm positioning to second row
 

Yes, I know.

Have a look at ScreenRes function in my original reply to OP
(see original post, quoted at the bottom),

ScreenRes reads the horizontal and vertical setting directly
via GetDeviceCaps api call, most often it will return 96 or 120.
1 inch = 72 points (by definition) = 96 pixels (or 120 pixels)

(Ofcourse) both methods achieve the same result..
but why do we need API's to position a simple userform?


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Michel Pierron wrote :

Re keepITcool,

Except for the menu bars, Excel uses points while the API functions
uses pixels.
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Sub Ratio()
Dim hwnd&, R As RECT, msg As String
hwnd = FindWindow(vbNullString, Application.Caption)
GetWindowRect hwnd, R
MsgBox "Points/Pixels Ratio: " _
& Application.Width / (R.Right - R.Left) _
& vbTab & "- (3/4)" & vbLf & "Pixels" _
& "/Points Ratio: " & Format((R.Right - R.Left) _
/ Application.Width, "0.00") & vbTab & "- (4/3)", 64
End Sub

Regards,
MP

"keepITcool" a écrit dans le message de
.com...
Michel,

Good call!

You are correct. I forgot to adjust the Top/Left props that I read.
(which I didnt see as I position near the top/left corner.. so the
converted numbers are small)

I notice you use a 'hardcoded' 3/4 4/3 conversion (equivalent to
72/96 and 96/72). I assume it's for explanation only.

I'll stick to using the actual screenres iso assuming all users are
set to the default 96dpi.


I've amended my demo as follows.

Private Sub UserForm_Activate()
'position under active cell
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(ActiveCell(2, _
1).Top * ScreenRes(0) / 72) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(ActiveCell(1, _
2).Left * ScreenRes(1) / 72) * 72 / ScreenRes(1)
End With
Repaint
Application.Wait (Now + TimeSerial(0, 0, 2))
'position on row2,col2 of visible range
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top * ScreenRes(0) / 72) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
2).Left * ScreenRes(1) / 72) * 72 / ScreenRes(1)
End With

End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Michel Pierron wrote :

Hi keepITcool,
For me, it misses the preliminary conversion of the co-ordinates
of range in pixels.

Sub AlignToRange()
'Const PixelToPoint = 3 / 4
'Const PointToPixel = 4 / 3
Dim Rng As Range
Set Rng = Range("B2")
With UserForm1
.StartUpPosition = 0
.Left = ActiveWindow.PointsToScreenPixelsX _
(Rng.Left * 4 / 3) * 3 / 4
.Top = ActiveWindow.PointsToScreenPixelsY _
(Rng.Top * 4 / 3) * 3 / 4
.Show
End With
End Sub

MP

"keepITcool" a écrit dans le message de
ft.com...

Hi D..
Try following

You'll need the APIs as VBA thinks all monitors are 72dpi.
Now it even works on my dual monitor (vertical) setup.


'Insert this code to your form:
Option Explicit

Private Declare Function GetDC Lib "user32.dll" ( _
ByVal hwnd&) As Long
Private Declare Function ReleaseDC Lib "user32.dll" ( _
ByVal hwnd&, ByVal hDC&) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hDC&, ByVal nIndex&) As Long
Function ScreenRes&(iDir%)
Dim lDC&
Static res
If Not IsArray(res) Then
ReDim res(1) As Long
lDC = GetDC(0)
res(0) = GetDeviceCaps(lDC, 88&)
res(1) = GetDeviceCaps(lDC, 90&)
lDC = ReleaseDC(0, lDC)
End If
ScreenRes = res(iDir)
End Function


Private Sub UserForm_Activate()
With ActiveWindow
Me.Top = .PointsToScreenPixelsY(.VisibleRange.Rows( _
2).Top) * 72 / ScreenRes(0)
Me.Left = .PointsToScreenPixelsX(.VisibleRange.Columns( _
1).Left) * 72 / ScreenRes(1)
End With

End Sub








--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


z wrote :

I am using Excel in Office 2000. I need to show a userform
that is positioned with it's top aligned with the top of the
second visible row of the active worksheet. It also needs to
have it's left side aligned with the left side of the second
visible column.

It seems userforms use screen offsets, and Excel rows and
columns use the Inner offset of the worksheet. How do I
translate the row and column coordinates to screen offsets?
If the user has Excel not maximized, for example, or if there
are custom menu bars which scoot the top of the activesheet
down, then it becomes difficult to know what the UserForm
coordinates need to be.

D Zook