Freeze userform position relative to worksheet

[tbrock]

Hello all,

I have a spreadsheet (Windows 32-bit, Excel 2007) in which I have a userform successfully being placed where I want it (top left of cell B1).

But the userform remains fixed in its initial position when I reposition the spreadsheet.

I would like the userform to move with the spreadsheet as if it is welded to its initial startup position. So I would always be able to see what I see in the first photo regardless of where on the screen Excel is.

I have looked high and low - after a few hours, I was frustrated/determined enough to scroll thru all the pages here at Mr. Excel that came up in search. So far nothing for what I hope (and believe) is a trivial problem. Can some kind soul prove me correct?

Thank you,
Tom

 

[Logit]

I could be wrong ... but ... I don't believe you are using all of the code from Chip Pearson.

Here is the download link : SetParent.xls

 

[tbrock]

You are correct, sir. His code covered 4 situatons and he assigns lots of labels to show what is going on. I have one case and don't need the labels, so I (believe) I have the essential code for my purposes. Originally I had his complete code incorporated and (I believe) the problem existed even then.

Perhaps...instead of incorporating HIS code into MY spreadsheet, I could reverse that and try incorporating the (probably) Yin Cognyto code into his... If that works, then clearly I have an error somewhere.

 

[Legacy 456155]

Hi Tom. You may need to translate coords using the ScreenToClient API function because the userform is positioned based upon the parent window's client area which is no longer the desktop. You should be able to get the coords of some range using ActiveWindow.PointsToPixels. I was attempting to cook up an example, but ActiveWindow.PointsToPixelsX is not behaving as I expected. In searching about that issue, I did see some code out there that uses other methods that you may want to give a try. Here's the relevant code and an example file that might give you some ideas. For the sake of simplicity, I pasted in an example that should work with a bare-bones userform. I did not take the time to make this compatible with 32 bit systems. The example file is set up a bit differently and provides an example of positioning the userform before showing it.

See PositionFormToRange.xlsm in this folder.

Option Explicit

Private Type POINTAPI
    X As Long
    Y As Long
End Type

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongLong, ByVal hWnd2 As LongLong, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongLong
Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongLong) As LongLong
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32.dll" (ByVal hDC As LongLong, ByVal nIndex As LongLong) As LongLong
Private Declare PtrSafe Function GetParent Lib "user32.dll" (ByVal hwnd As LongLong) As LongLong
Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As LongLong) As LongLong
Private Declare PtrSafe Function ReleaseDC Lib "user32.dll" (ByVal hwnd As LongLong, ByVal hDC As LongLong) As LongLong
Private Declare PtrSafe Function ScreenToClient Lib "user32.dll" (ByVal hwnd As LongLong, ByRef lpPoint As POINTAPI) As LongLong
Private Declare PtrSafe Function SetParent Lib "user32.dll" (ByVal hWndChild As LongLong, ByVal hWndNewParent As LongLong) As LongLong
Private Declare PtrSafe Function SetWindowPos Lib "user32.dll" (ByVal hwnd As LongLong, ByVal hWndInsertAfter As LongLong, ByVal X As LongLong, ByVal Y As LongLong, ByVal cx As LongLong, ByVal cy As LongLong, ByVal wFlags As LongLong) As LongLong

'check that Target is within the visible range
Private Sub PositionUserForm(Target As Range)
    Const SWP_NOSIZE = &H1
    Dim pt As POINTAPI, OffsetX As Long, OffsetY As Long, EXCEL7Hwnd As LongLong, UserFormHwnd As LongLong
   
    '"should" be the screen coords of the leftmost, visible range
    OffsetX = ActiveWindow.PointsToScreenPixelsX(0)
   
    '"should" be the screen coords of the topmost, visible range
    OffsetY = ActiveWindow.PointsToScreenPixelsY(0)
     
    pt.X = OffsetX + PointsToPixels(ActiveWindow.PointsToScreenPixelsX(Target.Left) - OffsetX, "X")
    pt.Y = OffsetY + PointsToPixels(ActiveWindow.PointsToScreenPixelsY(Target.Top) - OffsetY, "Y")

    IUnknown_GetWindow Me, VarPtr(UserFormHwnd)
    EXCEL7Hwnd = FindWindowEx(FindWindowEx(GetParent(UserFormHwnd), 0, "XLDESK", vbNullString), 0, "EXCEL7", vbNullString)

    SetParent UserFormHwnd, EXCEL7Hwnd
   
    'this function will translate screen coords to client (new parent) coords
    ScreenToClient EXCEL7Hwnd, pt
   
    'the userforms Left, Top, etc properties may no longer work as expected
    'the SWP_NOSIZE flag arg tells the function to ignore the height and width args
    SetWindowPos UserFormHwnd, 0, pt.X, pt.Y, 0, 0, SWP_NOSIZE
End Sub

Private Function PointsToPixels(Pts As Double, Axis As String) As Long
    Const WU_LOGPIXELSX = 88: Const WU_LOGPIXELSY = 90
    Dim hDC As LongLong
    hDC = GetDC(0)
    PointsToPixels = (Pts / (72 / GetDeviceCaps(hDC, IIf(Axis = "X", WU_LOGPIXELSX, WU_LOGPIXELSY)))) * (ActiveWindow.Zoom / 100)
    ReleaseDC 0, hDC
End Function

Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    PositionUserForm Range(InputBox("Enter a visible range address", , "B1"))
End Sub

 

[tbrock]

So I started with Chip Pearson's SetParent.xls instead of my own spreadsheet. If I do nothing more than click the option buttons...

1. Click "Active Window" throws an error for me and does nothing (I am guessing it is an Excel version error but I know nothing about API calls.)
2. Click "Application". The userform gets placed at [Application.Top -3], [Application.Left +5]
3. Click "Excel Desktop". The userform gets placed at [Application.top +125.25], [Application.Left + 4.25] <--- This is actually the position where I would like my userform to be IF the worksheet is maximized. But see below...

If, however, I manually move the userform and then click Application or Excel Desktop, the results change significantly and no longer seem tied to what I assume is the base position of useable space within the application/Excel spreadsheet. This appears similar to what is happening in my spreadsheet, so I am assuming it is not an error on my part. I also am assuming that this is due to using a different unit of measure after being set as a child to the application (as well as some sort of "memory" of the userform's previous position?) - although that would not explain why changing .top would have also changed .left as has happened.

(This is the "below" mentioned above) The remaining issue for me is that the two options that do that make the userform a child of the application/Excel tie it to the workBOOK while I need the userform to be "childed" to the workSHEET. I believe (sigh) that this is the option (Active Window) that Chip provides that is not functioning for me (Excel 2007). However, he does have another version of this functionality (Pearson Software Consulting ) which does work for me and it is this code that I am currently using in my spreadsheet.

Dataluver: Although the code you just posted is similar to the code I am using from Yin Cognyto, it is not exactly the same. I'll see if I can get it running and if it solves the issue. Meanwhile, I have contacted Chip Pearson. I don't know that he will have time to delve into this, but one can always hope.

 

[Logit]

Sadly Chip passed away not long ago. His family agreed to keep the website up and running for the benefit of VBA coders.

 

[tbrock]

Thank you, Logit, for the sad news.

I'd remove that from my comment, but I am not able to.

 

[tbrock]

Dataluver! Yes! At first blush the new code works perfectly for my needs.

The "basic" conversion for 64-bit declarations to 32-bit declarations:

1. Delete "PtrSafe" from the declaration
2. Change all occurrences (throughout the code) from LongPtr (or LongLong as was the case here) to Long

Those two steps got this working in Excel2007. There is at least one hiccup so far when I try to cancel input. Still playing, but this looks like the answer for me. Hopefully I'll post final results soon.

 

[Legacy 456155]

Chip Pearson will continue helping many of us for years to come, no doubt! I was warmed by your comment regarding him.

As for the example I gave you... It is minimal and does not account for multiple window panes and is just to get you hopefully headed in the right direction. The example download will show you how to make the code more useful by detaching it from a specific user form.

 

[tbrock]

Dataluver The code you last posted is working! To whom do I cite as the author?

Now that this part of my little project seems stable, I am polishing up the rest. Is it customary here to upload the "finished product" (or a link to it) in case it might help others down the road?

 

[Legacy 456155]

To whom do I cite as the author?

There isn't really anything original in that code. No need to cite.

Is it customary here to upload the "finished product" (or a link to it) in case it might help others down the road?

I don't know of any custom, but a link to your file might be nice for others who run into similar challenges. ?
Post back if you have any problems.