Positioning a Userform Over a Cell

keepITcool has developed a method to position a userform over a specific range. Finding a range’s screen coordinates is tricky. I usually resort to “close enough”.

He says:

First I found a bug. Excel 97 thru 2007.
The VisibleRange of Pane 2 and 3 (in a 4 pane window) are inconsistent.
depending on the sequence in which the vertical and horizontal split bars are set it will sometimes
return the range of the upper-right pane, sometimes the lower-left… and in both panes it may return activepane.index = 2
I found the cause and a workaround.

I soon got desperate using PointsToScreenPixels with non-standard zoom. Then I found that using XLM macro’s is the only reliable way to consistently get the “crosshair” on the screen under a variety of splits, zooms and display options. Some fiddling to offset from that point in case of frozen panes.. et voila!

You can download RangePos Beta1.zip.

Update: Download RangePos Beta3.zip

Posted in Uncategorized

18 thoughts on “Positioning a Userform Over a Cell

  1. Thanks Rob. You’d think after all this time I would remember to put the http in front of email addresses. If I don’t, WordPress sticks this site’s URL in front. The original link is now fixed.

  2. If the top-left pane is scrolled down a few rows, say to row 10, and you click in the bottom-left pane in cell B20, the form appears in the wrong place.
    I’m running Excel 2002 SP3 on Windows XP.

  3. Inresting,
    (win xp, xl 2000, 1060/768)
    Jurgen form did not postion correctly over the selected cell, and was not quite the right size, although the size did change with diffrent cell sizes.

    Chips[http://www.cpearson.com/Excel/FormPosition.htm] postioned correctly with out splits but not with.

    If i get some time I might have a play.

    Cheers
    Ross

  4. I sent Dick this Beta1 on July 16th…
    In the meantime I found some probs myself and tried to iron them out.. but never bothered to mail the current Beta3.

    Jurgen

  5. Vertical position does not work properly. Switching to 75% makes mouse behave strange way – it seems I have no control over it. Windows XP, Excel 2000.

  6. Thanks, keepITcool,

    This works well enough that I’ve incorporated it into a project.

    I’m impressed that it works on my multi-screen setup, since even Excel can’t always figure out where to pop up its dialogs. As for the few cases where the form doesn’t exactly show where I want it to, well, close enough…

    Thanks again.

    Dave

  7. There is a possibility with the RangeFromPoint method. By moving a virtual point (X,Y) by a little value and stop when this point meet the cell where you want to put your userform.

  8. Hi,

    I have kept some values in 10 rows X 3 columns array (and there are 4 such ranges Rng1, Rng2, Rng3 and Rng4). The 1st column contains the name of the text boxes on a User Form. The 2nd Column contains the width of the Text box (in centimeters)and 3rd column contain height of the text box (in centimeters). I need to select the name of range from a validation list in Cell A1, and the text box dimensions on the user forms need to change accordingly. Will anyone please help/guide me how to do this ?

    Best Regards
    CA Kanwaljit Singh Dhunna

  9. This is fabulous code. It would take me a long time to figure out how it’s working. I think I’ll just make a class module out of it to use it in my project.

    Many, many thanks!!

  10. Old string, This is exactly what I am looking for.

    tried in full screen mode and it gave me the most accurate positioning

    the position of the form is off by the height of the window title bar with h “Ctrl S” is spot on over the cell
    the position of the trace is is off by the height of the window title bar
    the position of the pane origin is is off by the height of the window title bar

    I think that the y pos needs to be adjusted by the height of the titlebar + the hieght of the menus, formula bar, and any other extraneous objects above the top of the sheet.

    Still screwy when I move the main excel window to my second monitor the cursor and form pop up as if it is on the first monitor.

    I will play with it to see if I can get it to be more accurate

  11. Dick, awesome work posting that file / code for Beta3 from keepITcool! A life saver!

    Wow – many thanks –
    An old post and forum but what an awesome solution for positioning my Userform at a specific cell location which takes into account ribbon status,formula view status, Heading Status, and Frozen panes. OMG – awesome!

    I have spent many house trying to solve the issue, reviewed and used Chip Pearsons code from 2002/3 however it never considered hidden rows, frozen panes or the view status of the Excel 2013 ribbon or headings! The code is fab and was up and running in my code in a mater of minutes and tweaked 2 minutes later!

    Works great for me on Multi-monitor

    Doesn’t take into account the zoom factor however a simple calculation for a “position adjustment” based on the zoom factor sorts this.! Perfect!

    Thanks to the original developer and poster! Appreciate your time and effort!

    Boris

  12. Hmmm. I’m trying this using Excel 2013 with multiple monitors. When I try it with Excel in monitor 1, the userform always appears top left. And when I try it with Excel in monitor 2, the userform appears in the middle of monitor 1. Anyone have any code they can share that works with 2013/multiple monitors?

  13. How sad is it that they make you jump through these hoops to simply position a form accurately.

    Criminal really.

Leave a Reply

Your email address will not be published. Required fields are marked *