Form Position

[kenny3794]

All -

I've been working to create a pop-up calendar that does not use ActiveX, works across multiple excel versions, and looks virtually the same on all versions, supports keyboard interactions, is contained with a single file, and more. All is well, I have a few bugs to fix and ideas I still would like to implement (ISO week numbers, quick option for jumping to today, etc).

Anyhow, one of the biggest challenges that I have remaining, is how do I control placement of the form. I've gone with API calls to remove the title bar and make the pop-up modeless. So I expect some relatively fixed offset in placement. However, the Form placement appears relative to the screen edge, not the window edge.

I've tried Chip Pearson's FormPositioner, but without consistent success in Excel 2010. I have attached Calendar.xlsm I'm using.

Also, if someone wants to contribute to my little activity, I'll gladly take some assistance, just let me know. While other calendars are available, which is where I received inspiration, none of them seem to "fit in" with a well formated, functional spreadsheet.

Anyhow, all I'm looking for now is form placement. Thanks for any assistance.

[Mordred]

Hi Kenny3794,

I like what you are doing here and I don't really have anything to add that helps with your project. In saying that, I ran it on my personal computer which uses Office Pro 2010 64, and it was relative to my screen just like you said. The calendar would stay running for me though, as soon as I hit Ctrl+F5 it flashed on and then off again. I stepped through the code (almost) line after line and it didn't error or trigger any error catches so I don't really know why it won't stay up. I am going to study this a bit though and try to figure out why it's doing that, unless someone else does.

Regards

[kenny3794]

Hi Kenny3794,

I like what you are doing here and I don't really have anything to add that helps with your project. In saying that, I ran it on my personal computer which uses Office Pro 2010 64, and it was relative to my screen just like you said. The calendar would stay running for me though, as soon as I hit Ctrl+F5 it flashed on and then off again. I stepped through the code (almost) line after line and it didn't error or trigger any error catches so I don't really know why it won't stay up. I am going to study this a bit though and try to figure out why it's doing that, unless someone else does.

Regards

I have seen no issue with pressing any invalid keys. Ctrl + F5 does nothing on my PC. The programming only responds to Arrow keys, Page Down, Page Up, ESC, and Enter. Anyone else able to help with the form positioning? Thanks.

[kenny3794]

Anyone else able to help with the form positioning? Thanks.

So, I had a semi-stroke of genious. I can use Application.Left and Application.Top to offset the position of the form with regards to the Application location.

HOWEVER, there remains two positioning issues that I cannot find information for. The first is while the RIBBON's state (ie expanded vs collapsed) affects placement. Also, the position of a cell is relative to cell A1, not on the display. Thus, if I fill this down to cell 200, then double click on cell D200, the form is off the screen!

EDIT: I noticed that ActiveWindow.VisibleRange has a .Top and .Left value. I can use these to get the form close, but still not exactly where I want it.

Again, any help is appreciated.