How do you resize a Multi Page User Form?

[Brian]

I have a Multi Page User Form that I need to be able to resize according to
Monitor Size and Resolution?

I saw on Chip Pearsons site some code for resizing a User form, but I am not
sure how to use it. I don't want to possibly mess up my computer settings.

It would be nice if my User Form would have a Min, Max & Close button like
most Windows apps have. Is it possible to get the User Form to Automaticaly
resize according to Monitor size and resolution?

 

[JLGWhiz]

UserForms have Top, Left, Width and Height properties that allow you to
position and size them with code during the initialize event. All are
measured in pixels so:

Private Sub UserForm_Initialize()
With Me
.Top = 25
.Left = 25
.Height = 500
.Width = 800
End With
End Sub

The above would take up most of the screen. But it is only an example of
how to use it.

 

[JLGWhiz]

Hi Brian, I tested this and it might suit your puirpose more that the
previous code.

Private Sub UserForm_Initialize()
Me.Height = Application.UsableHeight
Me.Width = Application.UsableWidth
End Sub

 

[Brian]

I tried it. I see how to adjust the height and width, but the others i didn't
see any change. Thiis adjust the Outer Main User Form Window, but the User
Form is still the same size. Is there a way to automate this process to
adjust both?

..Top = Adjust what? I changed it but nothing happens

..Left = Adjusts What? I changed it but nothing happens

..Height = Adjusts the User Form Window Height

..Width = Adjusts the User Form Window Width

 

[Brian]

Tried it too. 1/3 of the User Form is cut off.

How can I get the Min/Max Buttons on the User Form?

 

[JLGWhiz]

Afraid the max/min buttons are beyond my programming expertise. There are
no ready-made controls to put them on a UserForm that I know of. It might
be possible with some advanced programming to go into the source code and
extract the icons and functions to do it, but I am not that astute.

 

[JLGWhiz]

Here is some code with a user's comments that I copied off the web. As you
can see it goes into the application files to employ the controls.
===============
#1 02-26-2005, 11:06 AM
RB Smissaert
Guest Posts: n/a

Minimize and maximize buttons userforms

--------------------------------------------------------------------------

Please Register to Remove these Ads

How do I add minimize and maximize buttons to a VBA userform?
I have this code that does it, but when I change the caption of the
userform
the buttons don't work anymore. I am slowly coming to the conclusion
that
it probably is not worth the trouble and that it is better to make
your own
buttons.

Public Declare Function FindWindow _
Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As Long, _
ByVal lpWindowName As Long)

Private Declare Function SetWindowLong _
Lib "user32" _
Alias "SetWindowLongA" (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Private Sub UserForm_Initialize()

Dim hWnd as Long

hWnd = FindWindow(vbNullString, UserForm1.Caption)
SetWindowLong hWnd, -16, &H20000 Or &H10000 Or &H84C80080

End Sub

 

[JLGWhiz]

Thiis adjust the Outer Main User Form Window, but the User

Form is still the same size. Is there a way to automate this process to
adjust both?

I'm not sure what you are saying or asking here. Do you want to increase
the size of the controls on the userform as well? Are you expecting the
spacing between controls to adust? What?

At any rate, I believe that to get any other results each individual object
would have to be addressed.

 

[OssieMac]

Hi Brian,

Changing the userform size does not appear to change the size of the
controls within the userform. You need to use Zoom for that. The following
code tests for the video resolution and sets the useform Height, Width and
Zoom accordingly. However, some caveats. Video resolution Height and Width is
not proportional for all resolutions so there is some discreprencies in the
calculations, particulary with the Zoom, but it depends to some extent how
large you want the userform in relation to the usable screen size so you will
need to do some testing.

Note the comments. The API declaration must be at the top of the module
before any other subs. You will need to set the values depending on the
resolution of the screen on which the userform was developed.

'Video display code from the following lin
'http://spreadsheetpage.com/index.php/site/tip/determining_the_users_video_resolution/

'API declaration (At top of module)
Declare Function GetSystemMetrics32 _
Lib "user32" _
Alias "GetSystemMetrics" _
(ByVal nIndex As Long) As Long

Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1


Sub Show_Userform()
Dim vidWidth As Long
Dim vidHeight As Long
Dim dblMultWdth As Double
Dim dblMultHt As Double
Dim dblZoom As Double

vidWidth = GetSystemMetrics32(SM_CXSCREEN)
vidHeight = GetSystemMetrics32(SM_CYSCREEN)

'1152 and 864 is initial setup resolution
'Edit to your setup screen resolution
dblMultWdth = vidWidth / 1152
dblMultHt = vidHeight / 864

dblZoom = (dblMultWdth + _
dblMultHt) / 2 'Average

With UserForm1
'Lookup in Help for other options
'for StartUpPosition. Needs to be manual
'for setting left and top positions.
.StartUpPosition = 0
.Zoom = 100 * dblZoom
.Width = .Width * dblMultWdth
.Height = .Height * dblMultHt
.Top = 200
.Left = 100
.Show
End With

End Sub

 

[Brian]

All this code goes in a module, then I reference it from the User Form Int?

Can you help me set this up correctly please?

 

[Brian]

I bet if you thought about it you could do it. I have a fear of the unknown,
so API setting are intimadating to me, but I am sure you at lease know what
they are and how they can effect your computer.

I am not that good at this VBA stuff yet, but I am learning more and more
everyday thanks to some excelent help from people like yourself. You guys
really have a good grasp on this stuff.

I want to thank you for all your help.

 

[Brian]

Have you ever seen the code on Chip Pearsons site for doing this? It has
provisions for Min/Max/Resizing Control etc... My problem is that the code
is very intimadating to me because I don't know enough about it. I am sure it
works well, but I wouldn't have a clue how to even set it up let alone
trouble shoot it.

http://www.cpearson.com/excel/formcontrol.aspx

 

[OssieMac]

Hi Brian,

Yes. The code goes in a standard module. It was designed to be the code that
shows the userform so whatever code you are using to show the userform, you
need to incorporate it with it. If necessary you can call it from other code
with the following line of code.

Call Show_Userform

Without seeing you entire project it is difficult to help with how you need
to set it up with your project.

I'm declining to get involved with Chip Pearsons's code.

 

[Brian]

I am gett an error message on the
vidWidth = GetSystemMetrics32(SM_CXSCREEN)

Run time erro 453
Can't find DLL entry point GetSystemMetrics32 in user 32

I am about to give up on this.

 

[OssieMac]

Hi again Brian,

Don't give up. Defer it for a while and get the rest of your project working
but there is a real euphoria in achieving your goal.

You did follow the instructions and put all of the following part (that I
have now placed between asterisk lines) at the top of a standard module?

'******************************************
'API declaration (At top of module)
Declare Function GetSystemMetrics32 _
Lib "user32" _
Alias "GetSystemMetrics" _
(ByVal nIndex As Long) As Long

Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
'*******************************************

It certainly works with Excel 2002 on Windows XP operating system and with
Excel 2007 on Windows Vista operating system.

If you can't get it to work then you might like to try the following
solution. I didn’t have this solution previously but it appears to work just
as well as my other solution. The only problem is that the screen size
returns different dimensions to the screen resolution and you need to run a
little test program on the development computer to determine the returned
dimensions to use when calculating the proportions. When you get these, you
can delete that sub routine. (Keep a copy for future reference).

Run this sub first on the development computer and make a note of the
returned results.

Sub CurrentDimensions()
'Run this on the computer that is used to
'create the form to obtain initial dimensions
'for the Sub ShowUserform1()

Dim vidWidth As Double
Dim vidHeight As Double

'Maximize the window
Application.WindowState = xlMaximized

'Maximize the worksheet
ActiveWindow.WindowState = xlMaximized

'Get the dimensions
vidWidth = Application.Width
vidHeight = Application.Height

'Show a message box
MsgBox "Excel's window size is: " & vbLf & _
"vidWidth = " & vidWidth & vbLf & _
"vidHeight = " & vidHeight

End Sub

Now use the following code to show the Userform. (You can rename the sub or
incorporate the code in another sub but it needs to be run immediately prior
to the Userform Show code which you will see is the last line of the code.)

As previously there is some inaccuracy with the Zoom because screen
resolutions are not necessarily proportional but with my testing, if you use
screen resolutions that do not distort images then it is fairly good.

I physically measured the form on the screen using a ruler and the form was
the same size for every screen resolution.

Note the comments and edit where required.

Sub ShowUserform1()
Dim vidWidth As Double
Dim vidHeight As Double
Dim dblMultWdth As Double
Dim dblMultHt As Double
Dim dblZoom As Double

'Maximize the window
Application.WindowState = xlMaximized

'Maximize the worksheet
ActiveWindow.WindowState = xlMaximized

'Get Width and Height dimensions
vidWidth = Application.Width
vidHeight = Application.Height

'Proportional dimensions
'Edit 870 and 632 to the dimensions returned
'using Sub CurrentDimensions()
dblMultWdth = vidWidth / 870
dblMultHt = vidHeight / 632

'Edit 632 to the returned dimension.
'Proportional Zoom based on height
dblZoom = vidHeight / 632

‘Edit Userform1 to your Userform name
With UserForm1
'Lookup in Help for other options
'for StartUpPosition. Needs to be manual
'for setting left and top positions.
.StartUpPosition = 0 'Manual
.Zoom = 100 * dblZoom
.Width = .Width * dblMultWdth
.Height = .Height * dblMultHt
.Top = 200
.Left = 100
.Show
End With

End Sub

 

[Brian]

This is the code i pasted for the resize. It is located in the Sheet1 which
is the page the User Form is on.Is that where it is supposed to be or was it
to go into a module?

'API declaration (At top of module)
Private Declare Function GetSystemMetrics32 _
Lib "user32" _
Alias "GetSystemMetrics" _
(ByVal nIndex As Long) As Long

Private Const SM_CXSCREEN = 0
Private Const SM_CYSCREEN =
------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim vidWidth As Double
Dim vidHeight As Double
Dim dblMultWdth As Double
Dim dblMultHt As Double
Dim dblZoom As Double

'Maximize the window
Application.WindowState = xlMaximized

'Maximize the worksheet
ActiveWindow.WindowState = xlMaximized

'Get Width and Height dimensions
vidWidth = Application.Width
vidHeight = Application.Height

'Proportional dimensions
'Edit 870 and 632 to the dimensions returned
'using Sub CurrentDimensions()
dblMultWdth = vidWidth / 1446
dblMultHt = vidHeight / 816

'Edit 632 to the returned dimension.
'Proportional Zoom based on height
dblZoom = vidHeight / 632

'Edit Userform1 to your Userform name
With UserForm1
'Lookup in Help for other options
'for StartUpPosition. Needs to be manual
'for setting left and top positions.
.StartUpPosition = 0 'Manual
.Zoom = 75 * dblZoom
.Width = .Width * dblMultWdth
.Height = .Height * dblMultHt
.Top = 2
.Left = 35
.Show
End With

'UserForm1.Show
End Sub

 

[OssieMac]

Hi Brian,

Public declarations must be at the TOP of a STANDARD module and you cannot
change the Public declarations to Private. Therefore the code below goes at
the top of a standard module and do not change anything in the declarations.
(I have now included STANDARD module in the comment.)

I would not place the remainder of the code in a Worksheet_SelectionChange
event because if you do that it will run as soon as you click a cell on the
worksheet. (or perhaps that is what you want it to do). I think it would be
preferrable to place a command button on the worksheet and attach the code to
the button. Basically it is the Userform Show code and Userform1.Show must be
the last command in the Sub. (In my code it is incorporated in a With / End
With but it is still the last executable line of code.)

Option Explicit

'API declaration (At top of STANDARD module)
Declare Function GetSystemMetrics32 _
Lib "user32" _
Alias "GetSystemMetrics" _
(ByVal nIndex As Long) As Long

Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1