Very quick question. Userform control coordinates.

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Are in points? Correct? For example, the left coordinate of some control on a userform. Would be in points? I am trying to get the window handle for a frame control that is drawn onto a userform. Because the class name is the same for all msform windowed controls, I figured that I would use the WindowFromPoint API function to get the handle by figuring out the xy coordinates of the frame.

Thanks for any help!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#008000">'The width of a pixel in Excel's userform coordinates</font>
  <font color="#008000">'thanks to Stephen Bullen</font>
  <font color="#0000A0">Public</font> <font color="#0000A0">Property</font> <font color="#0000A0">Get</font> PointsPerPixelX() <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
       <font color="#0000A0">Dim</font> hDC <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
       hDC = GetDC(0)
      <font color="#008000"> 'A point is defined as 1/72 of an inch and LOGPIXELSX returns</font>
      <font color="#008000"> 'the number of pixels per logical inch, so divide them to give</font>
      <font color="#008000"> 'the width of a pixel in Excel's userform coordinates</font>
       PointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
       ReleaseDC 0, hDC
  
  
  <font color="#0000A0">End</font> <font color="#0000A0">Property</font>
  
  
  <font color="#008000">'The width of a pixel in Excel's userform coordinates</font>
  <font color="#008000">'thanks to Stephen Bullen</font>
  <font color="#0000A0">Public</font> <font color="#0000A0">Property</font> <font color="#0000A0">Get</font> PointsPerPixelY() <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
       <font color="#0000A0">Dim</font> hDC <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
       hDC = GetDC(0)
      <font color="#008000"> 'A point is defined as 1/72 of an inch and LOGPIXELSX returns</font>
      <font color="#008000"> 'the number of pixels per logical inch, so divide them to give</font>
      <font color="#008000"> 'the width of a pixel in Excel's userform coordinates</font>
       PointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
       ReleaseDC 0, hDC
  <font color="#0000A0">End</font> <font color="#0000A0">Property</font>
  
</FONT></td></tr></table>

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetDC <font color="#0000A0">Lib</font> "user32" (ByVal hwnd <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetDeviceCaps <font color="#0000A0">Lib</font> "Gdi32" (ByVal hDC <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> _
  nIndex <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> ReleaseDC <font color="#0000A0">Lib</font> "user32" (ByVal hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> _
  hDC <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> LOGPIXELSX = 88
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> LOGPIXELSY = 90
</FONT></td></tr></table>
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,771
Office Version
2016
Platform
Windows
Userform Controls are windowless so I don't think you can get their handles.

Regards.
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
The frame, listbox, and MultiPage controls are windows. The problem is the class name is the same for the client area and all of the above listed controls. So I have no way to distinguish one from the other or I could simply use the API FindWindowEx function. That is why I figured I would use the WindowFromPoint API function. Any other ideas?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
Tom

I think Jaafar is right.

What are you actually trying to achieve?
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Hi Jaafar and Norie. The question relates to this post.

It works, but you have to click on the frame. This way, I can use the cursor position to get frame1's handle by way of the frame1's click event. This may prove good enough for the OP. I don't know? My thought was to get the rectangle of the userform and then add the left position (converted from points to pixels) of the frame. What do you think?

ScreenCaptureControlOnUserform.zip

EDIT: See the code. There is a handle for the three controls listed.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
Tom

What is the OP trying to do?

I think there might be other solutions than the Windows API to place an image
from a userform on a spreadsheet.

I'm not going to try and check them out or look for them as it's rather late here, but I'll have a look in the morning/afternoon/whatever.:)
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
I don't know of any that would take a snapshot of a frame control and the child controls within? Maybe there is... I'm not even 100% sure that's what the OP wants. No matter, I do a lot of screenshots for various reasons but have been using Paint Shop Pro, which has a decent screen capture function, but I would like to create my own from Excel anyway. So this will be a good start.

Am still looking for an alternative suggestion for getting the window handle for the frame control on a userform. I'm betting there is a better way than using the WindowFromPoint API function.

Thanks guys!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
Tom

Sorry, I just reread your post and the link.:oops:

I think you might be on the right lines with the API, I can recall responding to
a similar post and directing the OP to some similar code.

I think that involved capturing some "freeform" drawing on a userform.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,771
Office Version
2016
Platform
Windows
I didn't know those particular controls had a hwnd !!

Anyway, have you thought about using the ClientToScreen API ?

Add a CommandButton1 to your form and add the following code in the userform module :

I assume the frame is named frame1

Code:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function WindowFromPoint Lib "user32" _
(ByVal xPoint As Long, ByVal yPoint As Long) As Long

Private Declare Function ClientToScreen Lib "user32" _
(ByVal hwnd As Long, lpPoint As POINTAPI) As Long
 
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
 
Private Type POINTAPI
    x As Long
    y As Long
End Type


'The width of a pixel in Excel's userform coordinates
  'thanks to Stephen Bullen
  Public Function PointsPerPixelX() As Double
       Dim hDC As Long
       hDC = GetDC(0)
       'A point is defined as 1/72 of an inch and LOGPIXELSX returns
       'the number of pixels per logical inch, so divide them to give
       'the width of a pixel in Excel's userform coordinates
       PointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
       ReleaseDC 0, hDC
   
   
  End Function

'The HEIGHT of a pixel in Excel's userform coordinates
  'thanks to Stephen Bullen

  Public Function PointsPerPixelY() As Double
       Dim hDC As Long
       hDC = GetDC(0)
       'A point is defined as 1/72 of an inch and LOGPIXELSY returns
       'the number of pixels per logical inch, so divide them to give
       'the width of a pixel in Excel's userform coordinates
       PointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
       ReleaseDC 0, hDC
   
   
  End Function



' displays the frame hwnd
Sub CommandButton1_Click()

    Dim P As POINTAPI
    Dim lngHwnd, lngFrameHwnd As Long
    Dim lngLeftInPixels, lngTopInPixels As Long
    
    lngHwnd = FindWindow(vbNullString, Me.Caption)
    lngLeftInPixels = Me.Frame1.Left / PointsPerPixelX
    lngTopInPixels = Me.Frame1.Top / PointsPerPixelY
    P.x = lngLeftInPixels
    P.y = lngTopInPixels
    ClientToScreen lngHwnd, P
    lngFrameHwnd = WindowFromPoint(P.x, P.y)
    MsgBox "The Frame handle is :  " & lngFrameHwnd

End Sub

Regards.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,833
Messages
5,446,765
Members
405,415
Latest member
Noodnutt

This Week's Hot Topics

Top