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

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

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

To calculate for a specified resolution you use the numbers that you know 
(i.e.800x600). To calculate for the auto-sized screen, wouldn't you just use 
the variables Application.Height, Application.Width, etc.?

"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

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

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

Roger,

UserForm1:  The "hide Excel" UserForm (i.e. your "background")
   - Also has a "CommandButton1" button
UserForm2:  The "main" UserForm (i.e. your "main")

Instructions:
1. Open a blank workbook
2. Open VBE

Do the following to the blank workbooks VBAProject:
3. Add two user forms
4. On the first user form (i.e. UserForm1), add a command button (i.e. 
CommandButton1) anywhere you like
5. Copy the "UserForm1 Code Module:" code portion (the code is below) and 
past it into the UserForm1 code window
6. Add a module (i.e. Module1)
7. Copy the "General/Standard Module Level Code:" code portion (the code is 
below) into the Module1 code window
8. Run Macro "ABC"

Let me know if I addressed your issue.

Best,

Matthew Herbert

'------------------------------------------------------------------------
UserForm1 Code Module:

Private Sub CommandButton1_Click()
    UserForm2.Show
End Sub

Private Sub UserForm_Initialize()
Dim sngPxl As Single
Dim sngHgt As Single
Dim sngWdt As Single
Dim sngHgtMid As Single
Dim sngWdtMid As Single

'get the screen size in order to size the user form
sngPxl = PointsPerPixel
sngHgt = SystemMetrics(smScreenHeight) * sngPxl
sngWdt = SystemMetrics(smScreenWidth) * sngPxl

'size the user form
With Me
    .Height = sngHgt
    .Width = sngWdt
    .Left = 0
    .Top = 0
End With

'get the center coordinates of the user form
sngHgtMid = sngHgt / 2
sngWdtMid = sngWdt / 2

'set the command button in the center
With Me.CommandButton1
    .Left = sngWdtMid - (.Width / 2)
    .Top = sngHgtMid - (.Height / 2)
End With

End Sub

'------------------------------------------------------------------------
General/Standard Module Level Code:

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As 
Long) As Long

Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal 
nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal 
hDc As Long) As Long

Private Const SM_CXSCREEN As Long = 0
Private Const SM_CYSCREEN As Long = 1

'Notes: Point = 1/72 Inches; Twip = 1/20 Point
Private Const LOGPIXELSX As Long = 88 'Pixels/Inch in X
Private Const LOGPIXELSY As Long = 90 'Pixels/Inch in Y
Private Const POINTS_PER_INCH As Long = 72

Public Enum SystemMetricsConstants
    smScreenWidth = SM_CXSCREEN
    smScreenHeight = SM_CYSCREEN
End Enum

Function SystemMetrics(SMC As SystemMetricsConstants) As Long
SystemMetrics = GetSystemMetrics(SMC)
End Function

Public Function PointsPerPixel() As Double
Dim lngHWndDC As Long
Dim lngDotsPerInch As Long

'if DC is NULL then GetDC retrieves the device context for the entire screen
lngHWndDC = GetDC(0)
lngDotsPerInch = GetDeviceCaps(lngHWndDC, LOGPIXELSX)
PointsPerPixel = POINTS_PER_INCH / lngDotsPerInch
ReleaseDC 0, lngHWndDC
End Function

Sub ABC()
UserForm1.Show
End Sub

"Roger on Excel" wrote:

> 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

Many Thanks Matthew,

I took a look at your code and modified it using the code I used at the top 
of the page :

Thus:

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

'set the command button in the center
With Me.cmdbutton1
    .Left = ((Application.Width - 140) / 2)
    .Top = ((Application.Height - 24) / 2)
End With

End Sub

140 is the width of the command button and 24 is the height.

Have a great weekend, and thanks again for your patience

Roger


"Matthew Herbert" wrote:

> Roger,
> 
> UserForm1:  The "hide Excel" UserForm (i.e. your "background")
>    - Also has a "CommandButton1" button
> UserForm2:  The "main" UserForm (i.e. your "main")
> 
> Instructions:
> 1. Open a blank workbook
> 2. Open VBE
> 
> Do the following to the blank workbooks VBAProject:
> 3. Add two user forms
> 4. On the first user form (i.e. UserForm1), add a command button (i.e. 
> CommandButton1) anywhere you like
> 5. Copy the "UserForm1 Code Module:" code portion (the code is below) and 
> past it into the UserForm1 code window
> 6. Add a module (i.e. Module1)
> 7. Copy the "General/Standard Module Level Code:" code portion (the code is 
> below) into the Module1 code window
> 8. Run Macro "ABC"
> 
> Let me know if I addressed your issue.
> 
> Best,
> 
> Matthew Herbert
> 
> '------------------------------------------------------------------------
> UserForm1 Code Module:
> 
> Private Sub CommandButton1_Click()
>     UserForm2.Show
> End Sub
> 
> Private Sub UserForm_Initialize()
> Dim sngPxl As Single
> Dim sngHgt As Single
> Dim sngWdt As Single
> Dim sngHgtMid As Single
> Dim sngWdtMid As Single
> 
> 'get the screen size in order to size the user form
> sngPxl = PointsPerPixel
> sngHgt = SystemMetrics(smScreenHeight) * sngPxl
> sngWdt = SystemMetrics(smScreenWidth) * sngPxl
> 
> 'size the user form
> With Me
>     .Height = sngHgt
>     .Width = sngWdt
>     .Left = 0
>     .Top = 0
> End With
> 
> 'get the center coordinates of the user form
> sngHgtMid = sngHgt / 2
> sngWdtMid = sngWdt / 2
> 
> 'set the command button in the center
> With Me.CommandButton1
>     .Left = sngWdtMid - (.Width / 2)
>     .Top = sngHgtMid - (.Height / 2)
> End With
> 
> End Sub
> 
> '------------------------------------------------------------------------
> General/Standard Module Level Code:
> 
> Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As 
> Long) As Long
> 
> Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
> Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal 
> nIndex As Long) As Long
> Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal 
> hDc As Long) As Long
> 
> Private Const SM_CXSCREEN As Long = 0
> Private Const SM_CYSCREEN As Long = 1
> 
> 'Notes: Point = 1/72 Inches; Twip = 1/20 Point
> Private Const LOGPIXELSX As Long = 88 'Pixels/Inch in X
> Private Const LOGPIXELSY As Long = 90 'Pixels/Inch in Y
> Private Const POINTS_PER_INCH As Long = 72
> 
> Public Enum SystemMetricsConstants
>     smScreenWidth = SM_CXSCREEN
>     smScreenHeight = SM_CYSCREEN
> End Enum
> 
> Function SystemMetrics(SMC As SystemMetricsConstants) As Long
> SystemMetrics = GetSystemMetrics(SMC)
> End Function
> 
> Public Function PointsPerPixel() As Double
> Dim lngHWndDC As Long
> Dim lngDotsPerInch As Long
> 
> 'if DC is NULL then GetDC retrieves the device context for the entire screen
> lngHWndDC = GetDC(0)
> lngDotsPerInch = GetDeviceCaps(lngHWndDC, LOGPIXELSX)
> PointsPerPixel = POINTS_PER_INCH / lngDotsPerInch
> ReleaseDC 0, lngHWndDC
> End Function
> 
> Sub ABC()
> UserForm1.Show
> End Sub
> 
> "Roger on Excel" wrote:
> 
> > 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

Roger,

Glad to be of help.  One more thought.  There is no need to hard code your 
"140" and "24".  If you were to ever change the width or height of the 
command button, then your button would no longer be "centered."  Instead, 
replace "140" with ".Width" and replace "24" with ".Height".  This is more 
robust than the hard coded values.

Best,

Matt

"Roger on Excel" wrote:

> Many Thanks Matthew,
> 
> I took a look at your code and modified it using the code I used at the top 
> of the page :
> 
> Thus:
> 
> Private Sub UserForm_Initialize()
> With Me
>         .Height = Application.Height
>         .Width = Application.Width
>         .Left = Application.Left
>         .Top = Application.Top
> End With
> 
> 'set the command button in the center
> With Me.cmdbutton1
>     .Left = ((Application.Width - 140) / 2)
>     .Top = ((Application.Height - 24) / 2)
> End With
> 
> End Sub
> 
> 140 is the width of the command button and 24 is the height.
> 
> Have a great weekend, and thanks again for your patience
> 
> Roger
> 
> 
> "Matthew Herbert" wrote:
> 
> > Roger,
> > 
> > UserForm1:  The "hide Excel" UserForm (i.e. your "background")
> >    - Also has a "CommandButton1" button
> > UserForm2:  The "main" UserForm (i.e. your "main")
> > 
> > Instructions:
> > 1. Open a blank workbook
> > 2. Open VBE
> > 
> > Do the following to the blank workbooks VBAProject:
> > 3. Add two user forms
> > 4. On the first user form (i.e. UserForm1), add a command button (i.e. 
> > CommandButton1) anywhere you like
> > 5. Copy the "UserForm1 Code Module:" code portion (the code is below) and 
> > past it into the UserForm1 code window
> > 6. Add a module (i.e. Module1)
> > 7. Copy the "General/Standard Module Level Code:" code portion (the code is 
> > below) into the Module1 code window
> > 8. Run Macro "ABC"
> > 
> > Let me know if I addressed your issue.
> > 
> > Best,
> > 
> > Matthew Herbert
> > 
> > '------------------------------------------------------------------------
> > UserForm1 Code Module:
> > 
> > Private Sub CommandButton1_Click()
> >     UserForm2.Show
> > End Sub
> > 
> > Private Sub UserForm_Initialize()
> > Dim sngPxl As Single
> > Dim sngHgt As Single
> > Dim sngWdt As Single
> > Dim sngHgtMid As Single
> > Dim sngWdtMid As Single
> > 
> > 'get the screen size in order to size the user form
> > sngPxl = PointsPerPixel
> > sngHgt = SystemMetrics(smScreenHeight) * sngPxl
> > sngWdt = SystemMetrics(smScreenWidth) * sngPxl
> > 
> > 'size the user form
> > With Me
> >     .Height = sngHgt
> >     .Width = sngWdt
> >     .Left = 0
> >     .Top = 0
> > End With
> > 
> > 'get the center coordinates of the user form
> > sngHgtMid = sngHgt / 2
> > sngWdtMid = sngWdt / 2
> > 
> > 'set the command button in the center
> > With Me.CommandButton1
> >     .Left = sngWdtMid - (.Width / 2)
> >     .Top = sngHgtMid - (.Height / 2)
> > End With
> > 
> > End Sub
> > 
> > '------------------------------------------------------------------------
> > General/Standard Module Level Code:
> > 
> > Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As 
> > Long) As Long
> > 
> > Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
> > Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal 
> > nIndex As Long) As Long
> > Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal 
> > hDc As Long) As Long
> > 
> > Private Const SM_CXSCREEN As Long = 0
> > Private Const SM_CYSCREEN As Long = 1
> > 
> > 'Notes: Point = 1/72 Inches; Twip = 1/20 Point
> > Private Const LOGPIXELSX As Long = 88 'Pixels/Inch in X
> > Private Const LOGPIXELSY As Long = 90 'Pixels/Inch in Y
> > Private Const POINTS_PER_INCH As Long = 72
> > 
> > Public Enum SystemMetricsConstants
> >     smScreenWidth = SM_CXSCREEN
> >     smScreenHeight = SM_CYSCREEN
> > End Enum
> > 
> > Function SystemMetrics(SMC As SystemMetricsConstants) As Long
> > SystemMetrics = GetSystemMetrics(SMC)
> > End Function
> > 
> > Public Function PointsPerPixel() As Double
> > Dim lngHWndDC As Long
> > Dim lngDotsPerInch As Long
> > 
> > 'if DC is NULL then GetDC retrieves the device context for the entire screen
> > lngHWndDC = GetDC(0)
> > lngDotsPerInch = GetDeviceCaps(lngHWndDC, LOGPIXELSX)
> > PointsPerPixel = POINTS_PER_INCH / lngDotsPerInch
> > ReleaseDC 0, lngHWndDC
> > End Function
> > 
> > Sub ABC()
> > UserForm1.Show
> > End Sub
> > 
> > "Roger on Excel" wrote:
> > 
> > > 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