I am sorry guys,

I read my post and I think I wasnt clear enough in describing my problem.

I use userforms to drive my spreadsheet. The main userform sits in the
center of the screen and is sized to fit a 1024/768 screen resolution. I set
it this way as most of the PC's I use have this resolution and if the
resolution is higher the userform sits neatly in the middle of the screen
using the correctly selected userform position stting.

Now, here is my problem. Behind the main userform is a background userform
which effectively hides excel in the background. I set this to activate at
the workbook startup.

This autosizes to fit the screen whatever the screen resolution is using the
code i posted at the start of this thread. This way the background is always
hiddem from view

On the background userform is a button to recall the main userform if the
user inadvertedly closes it.

I would like this to have its position dictated by code rather than manually
entering the position attributes. This way I could have the button always be
in the center of the autosizing background userform.

At present I can manually position it so it hides behind the main userform,
but as a perfectionist, i would like to explore better options to make it go
in the center.

What do you think?

Roger

"Matthew Herbert" wrote:

Roger,

As fisch4bill mentioned, Application.Height will give you the height of the
application. If the application is maximized then you essentially know the
screen resolution via Application.Height and Application.Width. Maybe I'm
just not understanding your situation. Is there more detail that you can
provide? (Or are you maybe referring to ths UserForm startup position, which
can be changed via the "StartUpPosition" property of a UserForm?)

(In general, if you are looking fo the screen resolution then you'll need to
use the GetSystemMetrics API. The argument 0, more so seen as "Private Const
SM_CXSCREEN As Long = 0" or some variation of a Const statement, gives you
the resolution of X; and the argument 1, more so seen as "Private Const
SM_CYSCREEN As Long = 1" or some variation of a Const statement, gives you
the resolution of Y. The API function declaration is "Private Declare
Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long". So,
GetSystemMetrics(SM_CYSCREEN), or GetSystemMetrics(1), will return the
resolution of Y).

Best,

Matt

"Roger on Excel" wrote:

Hi Mathew,

Thats fine for a particular screen resolution.

Is there a way to make the code adjust for screen resolution - the auto
sizing of the userform with the code i use works great, but i need the button
to adjust its position accordingly too.

Any ideas?

Roger

"Matthew Herbert" wrote:

Roger,

If you know the height and width of your command button, then after you set
the size of your UserForm (with the code you posted), do a little math to get
the center coordinates, do some more math to get the left and top coordiates
for the command button, and set the .Left and .Top properties of the command
button.

(You know half the height and half the width of the UserForm. You also know
half the command button height and half the command button width. The reset
is addition/subtraction).

Best,

Matthew Herbert
"Roger on Excel" wrote:

I have a userform which sizes to the screen using the following code

Private Sub UserForm_Initialize()
With Me
.Height = Application.Height
.Width = Application.Width
.Left = Application.Left
.Top = Application.Top
End With
End Sub

I have a command button on the userform, but I would like it to be always
centered on the page.

Is there a way to do this?

Many thanks,

Roger