userform screen placement

[dinotom]

Is there any way to tell a userform to always open in the center of the excel screen? It always seems to open somewhere in outer space on multiple screen setups.

 

[Norie]

Thomas

Userforms have a property called StartUpPosition which has the constant 1 - CenterOwner.

It's the default value, and in my experience a userform with that property set to that value opens up in the centre of the window it's being called from.

Mind you I'm not sure what you mean by multiple screen setups.

 

[dinotom]

well positions 1 and 2 open the form up in the center of your TOTAL screen space, ie the middle of the space whether its a 1 or 2 or 3 or 4 monitor setup. 0 is for manual start up position. Any idea how to use that so the form opens up on top of the excel workbook instead of somewhere else?

 

[Norie]

Thomas

Short answer - No.

I've never worked with multiple monitors with Excel, VBA or anything else.

 

[cedricthecat]

Interesting, as I didn't know I had a problem along the same lines, but after reading this post, I realise I do!

I've got a 22" widescreen monitor to the left and a 19" standard to the right

In one particular Workbook, if opened on the left screen, the Userforms open nicely in place over the Workbook. However, if I open Excel on the right screen and run the same Workbook, the Userform still opens on the left monitor!

(I think this might be a driver issue TBH, as if I use PaintShop Pro for example, the toolbars often end up on the wrong screen!)

 

[Hermanito]

This sounds very familiar...

I often have the luxury of using 2 or even 3 monitors while developing, one would typically show Excel itself, the other the VBE window (and email or browser on MrExcel on the 3rd )

The userforms however appear either over the Excel window, or over the VBE window, depending from where I start the macro. There also seems to be some memory effect like you describe as well... start a macro on screen 1, userform will show on screen 1, move Excel to screen 2, stop the macro, start the macro again, userform will again open on screen 1...
I have yet to find the system and logic behind it though

Excel isn't too good with handling multi-monitor setups, I like to use them during developing, afterwards, when using the application without the VBE open, and using only 1 monitor, it usually () goes well...

Sorry I couldn't give any detailed solutions...

 

[mthompso]

The following code will center the userform in Excel's window:

With UserForm1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With</pre>

 

[dinotom]

Unfortunately that code will only work for a one monitor setup. I am seeking a solution for multiple monitor setups

 

[mthompso]

It works for 2 monitors. I use it at work. We all have 2 monitors. The Excel program was displaying in the left monitor and the userform was displaying in the right monitor. After adding the above mentioned code, the Excel program and userform both display in the left monitor.

 

[ValueClickDataAnalyst]

I run two monitors at work and just tested the code, works like a charm. Not sure why it doesn't work for dinotom. Anyway, thanks mthompso.