Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window
You can automatically position your UserForm almost anywhere on your Excel application’s window, with the following Initialize event code that goes into the UserForm’s module.
Example 1, upper left
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Dim Top As Double, Left As Double
Top = Abs(Application.Top) + _
(Application.Height - ActiveWindow.Height) + _
(Application.UsableHeight - ActiveWindow.UsableHeight)
Left = Abs(Application.Left) + ActiveWindow.Width - ActiveWindow.UsableWidth
Me.Top = Top
Me.Left = Left
End Sub
The other 3 corners would be:
Upper right
Me.StartUpPosition = 0
Dim Top As Double, Left As Double
Top = Abs(Application.Top) + _
(Application.Height - ActiveWindow.Height) + _
(Application.UsableHeight - ActiveWindow.UsableHeight)
Left = Abs(Application.Left) + (Application.Width) - (Me.Width + 10)
Me.Top = Top
Me.Left = Left
Lower left
Me.StartUpPosition = 0
Dim Top As Double, Left As Double
Top = Abs(Application.Top) + _
(Application.Height) - (Me.Height + 10)
Left = Abs(Application.Left) + _
ActiveWindow.Width - ActiveWindow.UsableWidth
Me.Top = Top
Me.Left = Left
Lower right
Me.StartUpPosition = 0
Dim Top As Double, Left As Double
Top = Abs(Application.Top) + _
(Application.Height) - (Me.Height + 10)
Left = Abs(Application.Left) + _
(Application.Width) - (Me.Width + 10)
Me.Top = Top
Me.Left = Left
And, here’s an example for setting the position at the inset right side of the Excel window, while in the middle of the useable height, taking into account the height and width of the userform.
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = (Application.Height - Me.Height) / 2
Me.Left = (Application.Width - Me.Width - 50)
End Sub
Hey can pls provide try see can video option.
Thanks
1. Can please provide what codes do i use for saving and storing the input data keyed in by user.
2. I would like to verify the list of people, who had checked into file i had uploaded in share point for them to worked on.
Thanks a bunch!
Thanks for following my Excel blog page.
Wow, sorry I cannot assist with your SharePoint question, that is not my area of expertise. Regarding your first question, I imagine you want to do more than just save the file, which is what would take care of the “input data” portion of your question. Please explain in further detail what you are wanting to actually accomplish.
Hi,
I have three workbooks workbook A, workbook b & workbook C, & all workbook have userforms added. if open workbook A userform then i am not able to access workbook VB & workbook C.
if we open any userform that userform should’t come front of other workbook it should be fixed for specific workbook only. we can access workbook after closing opened userform only.
in short my question is i should able to access all A, B & C workbook if opened userform of any workbook. i shouldn’t need to close uerform to access another workbook
Try showing the userform modeless by placing a space and a zero after the Show statement. Example:
UserForm1.Show 0
Sir, i want to place the userform at bottom right edge of the selected cell. Is that possible? and how can i do that.
Thanks
Risky to do that because you need to take into account the userform’s height, in case it does not fit below the workbook window or inside the row headers. For example, if your lower right corner cell is B2, what then do you do?
This doesn’t position the userform relative to the screen.
This positions it relative to Excel’s *window*.
You are right. I got to thinking about this after seeing your comment. I characterized the viewing area as the screen because especially when a userform is involved, I always view the Excel project in its full window size which is the full screen. But of course reducing its window clarifies the distinction between the screen and the application window. I changed the nomenclature on this tutorial to reflect that distinction. Thanks for your comment calling my attention to this.
How can one do this according to the Active Cell position relevant to where it is on the visible window? So far all the code that I have tried for this takes into account the position of the active cell from A rather than where it is based on users scrolling which means that the position is not fixed correctly. Any ideas?
This was REALLY helpful to me! I spent a a number of hours trying to find how to position my “Floating Userform Button Menu” on my spreadsheet… When I tried your code it worked great! Thanks for the info and time you spent making this available to people like me!
Thank you for letting me know it worked for you, Geo! I also post Excel daily on Twitter @TomUrtis.
I use multiple monitors. These position the form in my total monitor area, regardless of which monitor I have my workbook showing. Is there a way of positioning the user form on the monitor that has Excel.
Right now I have excel in monitor 1. But the form keeps coming up in monitor 3. Very confusing for user.
Private Sub UserForm_Initialize()
‘PURPOSE: Position userform to center of Excel Window (important for dual monitor compatibility)
‘SOURCE: http://www.TheSpreadsheetGuru.com/the-code-vault
‘Start Userform Centered inside Excel Screen (for dual monitors)
Me.StartUpPosition = 0
Me.Left = Application.Left + (0.5 * Application.Width) – (0.5 * Me.Width)
Me.Top = Application.Top + (0.5 * Application.Height) – (0.5 * Me.Height)
End Sub
I have no idea what you have in monitor 3 which could be monitor 2 or monitor 1 at any given time and most importantly from a logic programming standpoint, why a monitor is THE monitor for showing the userform. Three monitors for looking at an Excel worksheet and you are wondering why it is confusing?
Multiple monitor fix
Private Sub UserForm_Initialize()
‘PURPOSE: Position userform to center of Excel Window (important for dual monitor compatibility)
‘SOURCE: http://www.TheSpreadsheetGuru.com/the-code-vault
‘Start Userform Centered inside Excel Screen (for dual monitors)
Me.StartUpPosition = 0
Me.Left = Application.Left + (0.5 * Application.Width) – (0.5 * Me.Width)
Me.Top = Application.Top + (0.5 * Application.Height) – (0.5 * Me.Height)
End Sub