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.

 

[Excelnt]

Thank you mthompso, this works perfectly and with a little fine tuning, you can put the form any place you like.

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

 

[gamma_ray]

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>

Thank you, mthompso!

I am working on a six-monitor dsplay and can fully attest this code works flawlessly! no matter which monitor screen the worksheet is on, calling a userform with this code brings it up right in the center of the worksheet!

gamma_ray

 

[kanyocsaba]

It works pretty good. I using 2 monitors in work.

 

[Heath]

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.

Do you have the following code preceding "With UserForm1"?

UserForm1.Show

I had the same issue using a dual monitor setup and deleted it. After that it worked.

 

[CarlDog]

Works like a charm!

 

[TryTek]

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.

The code does work just make sure you replaced the "UserForm1" name with the name of the Form you are using.

In a system with 2 or more monitors the Form will go to the monitor which has the VBE open or the last monitor where the VBE was located prior to closing the VBE.

mthompso has the right solution which will override the form object's position parameters.

 

[billritz]

The positioning calcs work if you set the forms StartUpPosition to 0, which is manual positioning, or, of course, if you only have one monitor. If you want it to be in the center of the application no matter where it is on multiple monitors, I'd suggest:
With frmUser
.StartUpPosition = 0
.Top = Application.Top + (Application.Height / 2) - (.Height / 2)
.Left = Application.Left + (Application.Width / 2) - (.Width / 2)
.Show
End With

 

[ldoodle]

Hi,

Hope it's ok piggy backing off of this thread.

I cannot get this to work. I have 3 monitors; a laptop (Surface Pro 5), a landscape monitor and a portrait monitor. ScreenPositioning is all over the place in VBA.

Using Debug.Print, this is what I found (Outlook was maximised each time):

Primary (middle screen, landscape):

Height: 1056
Width: 1936
Top: -8
Left: -8

Surface (left screen):

Height: 888
Width: 1384
Top: -2744
Left: 854

Portrait (right screen):

Height: 1896
Width: 1096
Top: 1912
Left: -387

This is how my screens are layed out in Windows: https://imgur.com/a/PlxWbD6

I would like to replicate the 'Save As' button. That always opens tucked to the top left of the ActiveWindow.

Thanks

 

[Johnny C]

great, it's a long time since this got first posted but it worked a treat 9 years later

 

[harveya915]

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

Still works!
Working on 3 monitors
Thanks!