Centralizing a command button on a userform

[Roger on Excel]

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

 

[Matthew Herbert]

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]

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

 

[fisch4bill]

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.?

 

[Matthew Herbert]

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]

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]

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]

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]

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