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
Rob van Gelder:
There might be a problem with the hyperlink Dick. Try this: download RangePos Beta1.zip
30 August 2007, 8:27 pmross:
Does Chip not already shave some code to do this?
31 August 2007, 6:48 amDick Kusleika:
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.
31 August 2007, 6:50 amRob van Gelder:
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.
2 September 2007, 4:17 pmI’m running Excel 2002 SP3 on Windows XP.
Ross:
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
3 September 2007, 6:13 amRoss
keepITcool:
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
4 September 2007, 8:42 pmsuseu:
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 September 2007, 12:39 amDM/Diddy:
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
11 October 2007, 11:26 amHollywood » Comment on Positioning a Userform Over a Cell by DM/Diddy:
[…] admin wrote an interesting post today onHere’s a quick excerptThanks, 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 … […]
11 October 2007, 7:27 pmHollywood » Comment on Positioning a Userform Over a Cell by Hollywood …:
[…] unknown wrote an interesting post today onHere’s a quick excerptadmin wrote an interesting post today onHere’sa quick excerptThanks, 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 … […]
12 October 2007, 6:52 am