VBA: Show userform in center of screen

[FryGirl]

I currently use the code below to center the userform in the center of the screen. All works good.

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

I have many userforms and instead of duplicating this code in the Sub to open every userform, can I call it somehow?

I'm just not sure how I would pass the name of the userform to the call to retrieve the position details.

 

[My Aswer Is This]

Yes, that's is perfect. Thank you very much.

In post 9 I changed Dim ans as long

Make that change.

 

[FryGirl]

Got it. Thanks.

 

[My Aswer Is This]

Got it. Thanks.

Glad I was able to help you. It's a learning experience for both of us some times.
I like challenges.

I have a userform with about 10 Multipage pages sorted by category
And I have links attached to option buttons I can click to go to web sites or open certain files on my computer.

And run certain scripts I use all the time.

I store this Userform in my Personal Workbook so it's available to me for showing on any workbook I may open.

Userforms are something I really like. And you can make them colorful also if you want.
Take care.

 

[Rick Rothstein]

I currently use the code below to center the userform in the center of the screen. All works good.

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

I have many userforms and instead of duplicating this code in the Sub to open every userform, can I call it somehow?

I'm just not sure how I would pass the name of the userform to the call to retrieve the position details.

I know you have gone in a different direction on this (MultiPage), but to address your original question above... no code is needed at all. When you create your UserForm, change its StartUpPosition in the Properties window to 2-CenterScreen and that UserForm will automatically appear in the center of the screen... no code required.

 

[My Aswer Is This]

Got it. Thanks.

If your going to remove the Combobox. How do you plan to hide the Multipage1.pages?
I thought that was one of the major objectives.

We could add that feature to the UserForm initialize code if you wanted. Only show the page associated with the sheet name.

 

[rlv01]

When you create your UserForm, change its StartUpPosition in the Properties window to 2-CenterScreen and that UserForm will automatically appear in the center of the screen... no code required.

That solution does not work well for a multiple monitor setup, which so many people have these days. I find I have to add code similar to the OP's in the activate event of any userform for any widely distributed workbook.

 

[FryGirl]

When you create your UserForm, change its StartUpPosition in the Properties window to 2-CenterScreen and that UserForm will automatically appear in the center of the screen... no code required.

Hi Rick, I will try this when back at work, but trying here at home with dual monitors it does not appear to work. I have dual monitors at work and so will the users.

 

[FryGirl]

It's a learning experience for both of us some times.

Indeed it is, and as you help lead me down this path, I think the process is shifting into the easiest to maintain (on my part).

​

If your going to remove the Combobox. How do you plan to hide the Multipage1.pages?

We could add that feature to the UserForm initialize code if you wanted. Only show the page associated with the sheet name.

I started out with the ComboBox idea only because that's what was stuck in my head. As this UserForm approach has opened my eyes, so has my idea for what's simplest for the user to interact with.

Maybe there isn't any need to hide the Multipages as they are all applicable and the user can jump back and forth amongst the pages as they deem necessary. The cherry on the top was to have the page to open that equals the active sheet.

Anyway, I think the simplest method is to use a Case statement structure on UserForm Initialize and let that chose the page by the activesheet.name.

Private Sub UserForm_Initialize()
    
    Select Case ActiveSheet.Name
    
        Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"
            MultiPage1.Value = 0
        
        Case "Master"
            MultiPage1.Value = 1
        
        Case "Analysis"
            MultiPage1.Value = 2
        
        Case "Level 1", "Level 2", "Level 3"
            MultiPage1.Value = 3
        
        Case "Summary"
            MultiPage1.Value = 4
        
        Case "Charts"
            MultiPage1.Value = 5
                
        Case "Forecating"
            MultiPage1.Value = 6
        
    End Select
    
End Sub

As you can see, the worksheet will have 20 tabs and the Multipage UserForm will have 7 pages.

 

[rlv01]

I have many userforms and instead of duplicating this code in the Sub to open every userform, can I call it somehow?

I just keep the code snippet handy and paste it into any new form I create. It's not much effort. If you change to this variation:

    With Me                           ' Center Screen on whatever monitor Excel is running on
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With

you can just paste it into any form without having to edit the form name each time (i.e. UserForm1, UserForm2, UserForm3, etc)

 

[FryGirl]

So just add that to the UserForm Initialize code?