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
There might be a problem with the hyperlink Dick. Try this: download RangePos Beta1.zip
Does Chip not already shave some code to do this?
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.
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.
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
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
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.
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
[…] 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 … […]
[…] 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 … […]
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.
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
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!!
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
Many thanks for this fabulous work. I was looking for so long to such a brilliant solution !
DIC
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
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?
How sad is it that they make you jump through these hoops to simply position a form accurately.
Criminal really.