marsou wrote:
Hi all,

The reason I am posting a new topic is because the thread where I'd
like to ask this question is about 2 month old, hence I cannot do it
there.
I only hope that those who were posting to the thread
http://groups.google.com/group/micro...5d146a1df45c75

will look here too.
However maybe somebody else could help me with this.
In that thread the question was how to get the screen coordinates of a
Range. And the solution was in using PointsToScreenPix*elsX and
PointsToScreenPix*elsY functions.
Now my problem here is that when I use this functions they are not
returning what I expect them to. For example if I get the screen
coordinates of Cell A1 and put there a user form, I was expecting that
the userform top left point will be at the top left of A1 cell. But it
is positioned a little lower and to the right. If I change the position
of the whole excel window and run the same macros, I expected that this
behaviour will be repeated, but now the form is displayed lower and
more to the right than the previous one - it looks like some
coefficient is multiplied.
I don't understand this, especially when I see from the posts I refered
to above that they didn't have this problem
So did anybody see this kind of behaviour from those functions?
Is it possible that it is because of 2003 excel which I am currently
using?
Or what am I missing here?
I really need help.. :(

Regards,
M


You could do this, which I found worked for sitations where the window
is maximised or minimised, and where the worksheet scroll is not in its
topmost position and when you have different sized toolbars.

There are still some magic numbers though, don't know where they came
from. The form name is UserForm1.

Option Explicit

Public Const SPI_GETNONCLIENTMETRICS = 41
Public Const SPI_SETNONCLIENTMETRICS = 42
Public Const LOGPIXELSX = 88
Public Const LOGPIXELSY = 90

Public Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long
Public Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hdc As Long) As Long
Public Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long
Public Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Public Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

private const MAGICX as long = 30
private const MAGICY as long = 48

Public Sub ActiveCellForm()
Dim Target As Range
Dim RangeLeft As Long
Dim RangeTop As Long
Dim SheetLeft As Long
Dim SheetTop As Long
Dim hdc As Long
Dim PixelsX As Long
Dim PixelsY As Long
Dim xlwindow As Long
Dim wndrect As RECT
Dim newForm As UserForm1
Dim nRet As Long

hdc = GetDC(0)
PixelsX = GetDeviceCaps(hdc, LOGPIXELSX)
PixelsY = GetDeviceCaps(hdc, LOGPIXELSY)
ReleaseDC 0, hdc

Set Target = ActiveCell

xlwindow = FindWindowEx(0, 0, "XLMAIN", Application.Caption)
xlwindow = FindWindowEx(xlwindow, 0, "XLDESK", vbNullString)
xlwindow = FindWindowEx(xlwindow, 0, "EXCEL7", ActiveWindow.Caption)

nRet = GetWindowRect(xlwindow, wndrect)
If nRet < 0 Then
SheetLeft = wndrect.Left
SheetTop = wndrect.Top

RangeLeft = MAGICX + SheetLeft + PixelsX * (Target.Left -
ActiveWindow.VisibleRange.Left) / 72
RangeTop = MAGICY + SheetTop + PixelsY * (Target.Top -
ActiveWindow.VisibleRange.Top) / 72

Set newForm = New UserForm1
newForm.Show 0
newForm.Left = 72 * RangeLeft / PixelsX
newForm.Top = 72 * RangeTop / PixelsX
End If
End Sub