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.

 

[billritz]

Glad to have helped

 

[sparts17]

This is great guys, and I've been using it consistently since I found it, the only problem is that it only works if there's only ONE excel sheet open per monitor. I'm working with two excel instances in the same window, and even with directly activating the workbook I want it to reference before running this code, the userform will still open centered in my other excel instance and I can't figure out how to reference ThisWorkbook with Application.Left/Right etc to get it to center in the workbook that the userform is issued from. The problem being that I have a userform that opens another userform. The first opens centered over ThisWorkbook, the second will open centered on the other workbook opened in the same monitor space. How do I get it to only open centered in the middle of the one instance I need to pinpoint?

 

[Bovinewaste]

Its 2021 and this still works. Brilliant.

 

[Monty238]

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! I'm using a three-monitor set-up, and this instantly fixed the problem for me.

 

[geoneo54]

The posted solution works unless you have a code window open on another monitor. If you do it will open in that window. At least that's what happened for me.