Archive for the ‘XLM’ Category.

Positioning a Userform Over a Cell

30th August 2007, 08:18 pm by Dick Kusleika

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

Category: Userforms and Controls, XLM  |  10 Comments