Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Hi Marsou,
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. We explain how to do this on page 325-326 of our book. Basically, you create a embedded chart object at the range, then immediately delete it. Excel has a window with a class name of EXCELE, which will be where the chart object was, so we can use API calls to read the position of that window (using FindWindowEx to get the handle and GetWindowRect to read its position), then show the form there. Regards Stephen Bullen Microsoft MVP - Excel Professional Excel Development The most advanced Excel VBA book available www.oaltd.co.uk/ProExcelDev |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Guys, thank you very much for your answers and tips!!
I'll surely try those first thing at work But I'd really like to know what is wrong with the API in my case.. Is it OK that it doesn't work?? Regards, M David Welch wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Hi,
how about the example in my post in the following thread: http://groups.google.com/group/micro...a3b66508433a37 -- HTH, okaizawa 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 PointsToScreenPixelsX and PointsToScreenPixelsY 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Hi okaizawa,
Thank you very much for your response!!!! As a matter of fact your solution solved my problem. But I have a couple of questions for you and I would highly appreciate if you could answer me. The main part of your solution is: x = ActiveWindow.PointsToScreenPix*elsX(0) For i = 1 To r.Column - 1 x = x + Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001) Next y = ActiveWindow.PointsToScreenPix*elsY(0) For i = 1 To r.Row - 1 y = y + Int(ws.Rows(i).Height * py * z / 7200 + 0.5000001) Next Me.Left = x * 72 / px Me.Top = y * 72 / py In numbers. for cell "A1", Zoom = 100 , LOGPIXELSX = 96 and LOGPIXELSY = 96 I get: x = 28; y = 157 from PointsToScreen-s and after the conversion suggested: x=21; y=117.75 Here are my questions what do these numbers mean: 7200, 0.5000001, 72? And another one: I am returning to my "bete noir" PointsToScreenPix*elsX and PointsToScreenPix*elsY functions. Aren't they supposed to return the coordinates of exact excel point(even if conversion needed). I mean why start with 0,0 point and then add all the widths(heigths) up to selected range? If you could answer my questions(especially the first one, because I'd like to know what exactly did solve my problem :) )... However thank you VEEEEEEERY much for your help. Waiting for your answer, Regards, M okaizawa wrote: Hi, how about the example in my post in the following thread: http://groups.google.com/group/micro...a3b66508433a37 -- HTH, okaizawa 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 PointsToScreenPixelsX and PointsToScreenPixelsY 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Hi,
"Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)" is equivalent to Application.Round(ws.Columns(i).Width * (z / 100) * (px / 72), 0) "* (z / 100)" means zooming. "* (px / 72)" converts from points to pixels. "Application.Round( ,0)" rounds to an integer. this code converts each row and column, not a whole range. it is just one of hypotheses about the design of PointsToScreenPixelsX and PointsToScreenPixelsY. I don't know the reason actually. the exact coordinates have been calculated so far on my pc in this way. -- HTH, okaizawa marsou wrote: Hi okaizawa, Thank you very much for your response!!!! As a matter of fact your solution solved my problem. But I have a couple of questions for you and I would highly appreciate if you could answer me. The main part of your solution is: x = ActiveWindow.PointsToScreenPixÂ*elsX(0) For i = 1 To r.Column - 1 x = x + Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001) Next y = ActiveWindow.PointsToScreenPixÂ*elsY(0) For i = 1 To r.Row - 1 y = y + Int(ws.Rows(i).Height * py * z / 7200 + 0.5000001) Next Me.Left = x * 72 / px Me.Top = y * 72 / py In numbers. for cell "A1", Zoom = 100 , LOGPIXELSX = 96 and LOGPIXELSY = 96 I get: x = 28; y = 157 from PointsToScreen-s and after the conversion suggested: x=21; y=117.75 Here are my questions what do these numbers mean: 7200, 0.5000001, 72? And another one: I am returning to my "bete noir" PointsToScreenPixÂ*elsX and PointsToScreenPixÂ*elsY functions. Aren't they supposed to return the coordinates of exact excel point(even if conversion needed). I mean why start with 0,0 point and then add all the widths(heigths) up to selected range? If you could answer my questions(especially the first one, because I'd like to know what exactly did solve my problem :) )... However thank you VEEEEEEERY much for your help. Waiting for your answer, Regards, M |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Thank you very much, okaizawa
I guess now I need to go and dig into the theory. 72, 96, dpi etc. I don't know much about that. :") Thnx again for your help. Regards, M okaizawa wrote: Hi, "Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001)" is equivalent to Application.Round(ws.Columns(i).Width * (z / 100) * (px / 72), 0) "* (z / 100)" means zooming. "* (px / 72)" converts from points to pixels. "Application.Round( ,0)" rounds to an integer. this code converts each row and column, not a whole range. it is just one of hypotheses about the design of PointsToScreenPixelsX and PointsToScreenPixelsY. I don't know the reason actually. the exact coordinates have been calculated so far on my pc in this way. -- HTH, okaizawa marsou wrote: Hi okaizawa, Thank you very much for your response!!!! As a matter of fact your solution solved my problem. But I have a couple of questions for you and I would highly appreciate if you could answer me. The main part of your solution is: x = ActiveWindow.PointsToScreenPix*elsX(0) For i = 1 To r.Column - 1 x = x + Int(ws.Columns(i).Width * px * z / 7200 + 0.5000001) Next y = ActiveWindow.PointsToScreenPix*elsY(0) For i = 1 To r.Row - 1 y = y + Int(ws.Rows(i).Height * py * z / 7200 + 0.5000001) Next Me.Left = x * 72 / px Me.Top = y * 72 / py In numbers. for cell "A1", Zoom = 100 , LOGPIXELSX = 96 and LOGPIXELSY = 96 I get: x = 28; y = 157 from PointsToScreen-s and after the conversion suggested: x=21; y=117.75 Here are my questions what do these numbers mean: 7200, 0.5000001, 72? And another one: I am returning to my "bete noir" PointsToScreenPix*elsX and PointsToScreenPix*elsY functions. Aren't they supposed to return the coordinates of exact excel point(even if conversion needed). I mean why start with 0,0 point and then add all the widths(heigths) up to selected range? If you could answer my questions(especially the first one, because I'd like to know what exactly did solve my problem :) )... However thank you VEEEEEEERY much for your help. Waiting for your answer, Regards, M |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
And once again: X and Y Screen Coordinates of a Range
Ok, I've done my study :)
And with a little bit of mathematics - here is okaizawa's code update that allows to put the form at whichever excel coordinate that I want - without regard to any range: Private Declare Function GetDeviceCaps Lib "gdi32" ( _ ByVal hdc As Long, ByVal nIndex As Long) As Long Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long Private Declare Function ReleaseDC Lib "user32" ( _ ByVal hwnd As Long, ByVal hdc As Long) As Long Private Const LOGPIXELSX = 88 Private Const LOGPIXELSY = 90 Sub GetPositionInScreeenPoints(ByVal Left As Double, ByVal Top As Double, _ ByRef X As Double, ByRef Y As Double) Dim hdc As Long Dim PixelsPerInchX As Long Dim PixelsPerInchY As Long Dim PointsPerInch As Long Dim CurrentZoomRatio As Long hdc = GetDC(0) PixelsPerInchX = GetDeviceCaps(hdc, LOGPIXELSX) PixelsPerInchY = GetDeviceCaps(hdc, LOGPIXELSY) ReleaseDC 0, hdc PointsPerInch = Application.InchesToPoints(1) ' most of the time = 72 CurrentZoomRatio = ActiveWindow.Zoom / 100 X = ActiveWindow.PointsToScreenPixelsX(0) X = X + Left * CurrentZoomRatio * PixelsPerInchX / PointsPerInch X = Round(X, 0) X = X * PointsPerInch / PixelsPerInchX Y = ActiveWindow.PointsToScreenPixelsY(0) Y = Y + Top * CurrentZoomRatio * PixelsPerInchY / PointsPerInch Y = Round(Y, 0) Y = Y * PointsPerInch / PixelsPerInchY End Sub Thank you guys for your help! Regards, M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
X and Y Screen Coordinates of a Range !!! | Excel Programming | |||
Cell address nearest to screen coordinates | Excel Programming | |||
getting coordinates of a range | Excel Programming | |||
determining coordinates for screen placement of user form | Excel Programming | |||
Screen Coordinates of a given cell in XL | Excel Programming |