VBA - Centering Userforms

acc5149

New Member
Joined
Oct 28, 2014
Messages
26
Hey All,

I'm having trouble getting my userforms to center without having to re-initialize the form.

In my project, I have multiple goals and objectives and measures which make up each objective. Click on a button to go to a subform, update data, click another to return. Stuff like that.

Anyway, I have the startupposition set to 1 - CenterOwner. This works perfectly fine if I unload the form before showing it, ie:

frm1.Hide
Unload frm2
frm2.Show

However, when I update some of the subforms, I update a value on the previous form. Therefore, I do not want to unload that previous form or else it will not update with the new data.

Is there a way to center the userform without having to re-initialize the form? Let me know if my explanation makes sense!

Thanks!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I don't see that as an option when writing the code. I put it in as such:

frm2.Hide
frm1.Refresh
frm1.Show

And it came back with an error:

Compile Error:
Method or data member not found.


I've never seen that .refresh code as an option before. Usually I'll unload the form before showing it again, but in certain cases, I don't want to reinitialize the form because I have new data that I want to display instead of the older saved data that would be recalled by reinitializing.

Basically, when I go "back" to a previous form without reloading it, the form defaults over to the left side of my screen. The StartUpPosition is set to CenterOwner, so when you initialize the form it's centered. I don't know how to keep it centered when leaving the form and returning to it again without having to re-initialize it.
 
Upvote 0
Code:
Private Sub CommandButton1_Click()
  CenterUserform UserForm1
End Sub

Sub CenterUserform(uf As Object)
  With uf
    .Top = (Application.Height / 2) - (.Height / 2)
    .Left = (Application.Width / 2) - (.Width / 2)
  End With
End Sub
 
Upvote 0
Thanks! This code partially worked. It centers the form vertically. However, the form is on the far left of my screen. I'm thinking this could be because I'm opening this on a two monitor set up. It doesn't go directly in between both monitors, but it is on the left side of the right screen (which is where I have excel opened). I'm not sure exactly what the code is doing, so I'm not sure how to alter it to center it horizontally (clearly it would be with the ".Left" portion of the code though.

Also, is there a way to code it so that it would be centered regardless of which screen you have excel opened on? Or if you open it with a single monitor system?

Thanks!!!
 
Upvote 0
Any further help with this? The code I was given above works, but not for two monitors. It centers vertically, but is pushed to the left side of the screen I have excel open on.
 
Upvote 0
Actually, I've noticed that if I open excel on my default monitor (where my desktop is) then all the userforms always open in the center, regardless of the unload situation I mentioned earlier.

So with that, I'd like to alter my question. Is there a way to make the userform always open on the default monitor? For me it's my left monitor, but it could be the right monitor for a different user.
 
Upvote 0
After further tinkering, I change the startup position to 3 - Windows Default. However, now I want to center it on the screen. Using the application.height/width codes is dependent on which monitor excel is opened on. I want it to always be centered on the default monitor, regardless of which monitor you have excel open on. That way it can be consistent across all users' pcs. Any clues how to do that?
 
Upvote 0
Tinker and see if this helps:
Code:
If Application.Left > 1000 Then 
    With Me 
        .Top = (Application.UsableHeight / 2) + (Me.Height / 2) 
        .Left = (1.5 * (Application.UsableWidth)) - (Me.Width / 2) 
        .StartUpPosition = 3 
    End With 
Else 
    With Me 
        .Top = (Application.UsableHeight / 2) + (Me.Height / 2) 
        .Left = (Application.UsableWidth / 2) - (Me.Width / 2) 
        .StartUpPosition = 3 
    End With 
End If

This may help as well: https://social.msdn.microsoft.com/F...ened-in-multimonitor-environment?forum=isvvba
or
https://dutchgemini.wordpress.com/2...to-center-userform-correctly-on-dual-screens/
 
Upvote 0

Forum statistics

Threads
1,225,627
Messages
6,186,100
Members
453,337
Latest member
fiaz ahmad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top