Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
14 comments on “Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window
  1. Doris Choo says:

    Hey can pls provide try see can video option.
    Thanks

  2. Doris Choo says:

    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!

    • Tom Urtis says:

      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.

  3. kehsav says:

    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

  4. john says:

    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

    • Tom Urtis says:

      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?

  5. ashleedawg says:

    This doesn’t position the userform relative to the screen.
    This positions it relative to Excel’s *window*.

    • Tom Urtis says:

      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.

  6. Raymond says:

    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?

  7. Geo C says:

    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!

  8. cjroos says:

    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.

  9. Tom Urtis says:

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *

*