Form Position Chip Pearson

[Steven]

Chip,

I was working with your Form Position file and trying to incorporate it into
something I am doing. I have a file that uses:

Private Sub App_SheetBeforeRightClick .....

so that regardless of what file I am in UserForm1 will show on the
RightClick. If the excel file is a basic file with the cells the default
height and width then the form opens in the position like your file. But if
the file has more going on in it with different row heights and column widths
and word wrap etc... then the form does not open in the consistent spot as
with the standard file.

Is that to be expected or do I need to do more things?

Thank you,

Steven

 

[Chip Pearson]

I'll have a look at it to see what is going on. What version of Excel
are you using. If you have a workbook you can send me, send it to
(e-mail address removed)

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Peter T]

I'd be interested to know what the problem was as I also have a "display
form at cell location" app (I think totally different approach to Chip's
from memory). It appears to position correctly even with different
row/column sizes, wordwrap, etc, but maybe I have missed something.

Regards,
Peter T

 

[Chip Pearson]

I've played around with the FormPositioner on sheets with various
sized columns and rows and with merged cells. It works as I expected
it to in all cases. Can you provide a workbook that illustrates the
problem? I did make a small bug fix so there is a new version of the
code at http://www.cpearson.com/Excel/FormPosition.htm .

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Peter T]

Hi Chip,

I can't relate anything wrong with what the OP described - very nice!

One thing, maybe, with a tall row near the bottom of the window partially
extending below it, the form in it's default position can extend well below
the taskbar and partially hidden. But I think a relatively rare scenario,
easily catered for (SystemParametersInfo with SPI_GETWORKAREA).

FWIW, the approach I used was with PointsToScreenPixelsX(Y), seems to get
the cell's screen coordinates irrespective as to row/col sizes etc, even if
cell A1 is way off the screen. No need to look at toolbars, window positions
etc.

Regards,
Peter T