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.

 

[FryGirl]

Found it.

Post #4 here works.

Thank you for your time.

 

[My Aswer Is This]

Just curious.
Do you really need numerous UserForms?

Have you ever looked at putting Multipages on your UserForm?

A Multipage Page can be setup to look just like a Userform

Sort of like having numerous sheets in your Workbook

A Userform Multipage has page tabs just like Workbook sheet tabs.

Click on Page1 and see a different Multipage page which can have all sort of controls just like a workbook sheet has many controls if you want.

 

[FryGirl]

Well, the userforms are designed quite different, so not sure they would work in the Multipage setup, but something I can consider.

Yes, as far as Multipage userforms, you helped me about a week ago and it works great.

https://www.mrexcel.com/forum/excel-questions/1078729-userform-example-help-file-excel-workbook.html

Private Sub ComboBox1_Change()
    Dim Page As MSForms.Page
    Dim ans As String
    ans = ComboBox1.Value
     
    For Each Page In MultiPage1.Pages
        If Page.Caption <> ans Then
            Page.Visible = False
        Else
            Page.Visible = True
            MultiPage1.Value = 1
        End If
    Next
End Sub

Private Sub UserForm_Initialize()
    Dim Page As MSForms.Page
    For Each Page In MultiPage1.Pages
       ComboBox1.AddItem Page.Caption
    Next
    
    For Each Page In MultiPage1.Pages
        Page.Visible = False
    Next
End Sub

I do have a question though about the Multipage code above. I want to name all the Multipage tabs the same name as the workbooks tabs. Could I then have the Multipage open directly up to that tab name? I still want to keep the ComboBox, but have the page equal to the tab the user is on.

There actually would be a group of tabs that would not have their own tab in the Multipage UserForm. Those tabs will be named, 1, 2, 3, 4, 5, 6, etc. All of those tabs would have a tab on the UserForm just simply called Input Sheet.

Hope this makes sense.

 

[My Aswer Is This]

You can try this to name the tabs the same as the sheet name
So if you have 5 multipage pages each tab will get the same name as sheets 1 to 5 names.

See if this part does what you want.

Private Sub UserForm_Initialize()
'Modified  12/4/2018  7:06:20 PM  EST
Dim i As Long
i = 0
 For Each Page In MultiPage1.Pages
 i = i + 1
    ComboBox1.AddItem Page.Caption
    Page.Caption = Sheets(i).Name
 Next
End Sub

If not how will I know what sheets have tabs on the Multipage

I did not understand this:

There actually would be a group of tabs that would not have their own tab in the Multipage UserForm. Those tabs will be named, 1, 2, 3, 4, 5, 6, etc. All of those tabs would have a tab on the UserForm just simply called Input Sheet.
​

 

[FryGirl]

So, my tab names are Charts, Master, Analysis, Forecasting, and 1, 2, 3, 4, 5, 6, 7, 8

On the Multipage UserForm I will have a tab for each of the wks tabs named exactly the same. Each Multipage UserForm tab will have some help instructions for each wks tab. The wks tabs that are numbered are basically all the same, so they can have just one tab on the Multipage UserForm.

 

[FryGirl]

Your modification to the code in post #5 works great. The Multipage UserForm now shows the tabs as the same as name as the worksheet tabs.

Using this method, I can now get rid of the ComboBox.

ComboBox1.AddItem Page.Caption

Now, is it possible to have the UserForm open to the active tab name on the worksheet?

 

[My Aswer Is This]

Try this:

Private Sub UserForm_Initialize()
'Modified  12/4/2018  7:45:20 PM  EST
Dim i As Long
Dim ans As String
i = 0
 For Each Page In MultiPage1.Pages
    i = i + 1
    Page.Caption = Sheets(i).Name
    ComboBox1.AddItem Sheets(i).Name
  Next
    ans = ActiveSheet.Index
    
   MultiPage1.Value = ans - 1
End Sub

 

[My Aswer Is This]

Sorry:

Try this:

Private Sub UserForm_Initialize()
'Modified  12/4/2018  7:53:14 PM  EST
Dim i As Long
Dim ans As Long
i = 0
 For Each Page In MultiPage1.Pages
    i = i + 1
    Page.Caption = Sheets(i).Name
    ComboBox1.AddItem Sheets(i).Name
  Next
    ans = ActiveSheet.Index
    
   MultiPage1.Value = ans - 1
End Sub

 

[FryGirl]

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