Userform Size

Wes

Board Regular
Joined
Jan 30, 2004
Messages
195
Is there a way to scale down the size of the userform when it is activated?

Thanks,

Wes
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
In the userform's Activate event, you could set the height and width based on the size of Excel.
Code:
Private Sub UserForm_Activate()

Dim h1 As Single
Dim w1 As Single
h1 = Application.Height
w1 = Application.Width

UserForm1.Height = h1 * 0.75
UserForm1.Width = w1 * 0.75

UserForm1.Top = h1 * 0.125
UserForm1.Left = w1 * 0.125

End Sub
The problem is that you will have to move and scale your controls as well since I'm pretty sure there is no "AutoScale" option for a form.

Another issue is that Application.Height and Application.Width will be variable if Excel is not full-screen.

My solution to this (for various users with different resolutions) is to design to the smallest screen size. That said, I don't design for 640x480. Those folks will just have to up their resolution to 600x800...



K
 
Upvote 0
Thanks all,

The auto scale thing is what I need. I may need to work around this problem having a form running in the back ground and only giving the user a small form that has a numerical selection and the print form takes it's print information from that.

Thanks again,

Wes
 
Upvote 0
Hi Wes!

You Could try something this:

Place this code in Workbook Open:

Private Sub Workbook_Open()
Run "DisplayVideoInfo"
End Sub

Place this code in a module:

Declare Function GetSystemMetrics32 Lib "user32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Declare Function GetSystemMetrics16 Lib "user" _
Alias "GetSystemMetrics" (ByVal nIndex As Integer) As Integer
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
Sub DisplayVideoInfo()
Dim vidWidth As Integer, vidHeight As Integer
Dim Msg As String, Style As String
Dim Title As String, Response As String
If Left(Application.Version, 1) = 5 Then
vidWidth = GetSystemMetrics16(SM_CXSCREEN)
vidHeight = GetSystemMetrics16(SM_CYSCREEN)
Else
vidWidth = GetSystemMetrics32(SM_CXSCREEN)
vidHeight = GetSystemMetrics32(SM_CYSCREEN)
End If
If vidWidth = 1280 And vidHeight = 1024 Then
Sheets(1).Range("A1") = 600 'Set a sheet and a range where you can save this value
Sheets(1).Range("A2") = 800 'Set a sheet and a range where you can save this value
Else
If vidWidth = 1024 And vidHeight = 768 Then
Sheets(1).Range("A1") = 400 'Set a sheet and a range where you can save this value
Sheets(1).Range("A2") = 600 'Set a sheet and a range where you can save this value
Else
If vidWidth = 800 And vidHeight = 600 Then
Sheets(1).Range("A1") = 350 'Set a sheet and a range where you can save this value
Sheets(1).Range("A2") = 450 'Set a sheet and a range where you can save this value
End If
End If
End If
End Sub

And this code in UserForm Initialize:

Private Sub UserForm_Initialize()
With UserForm1
.Height = Sheets(1).Range("A1")
.Width = Sheets(1).Range("A2")
End With
End Sub

Create a sheet to store the values in, or select a "sheet.range"out of range for your work.
Change the values til you got the userform in the size you prefer.
Note: Cover all the screen resolutions you want to work with, otherwise your UserForm will be difficult to work with...

/Roger
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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