Userform Size

Wes

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

Thanks,

Wes
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Alan Nicoll

Board Regular
Joined
Feb 3, 2004
Messages
71
Set the height and width properties:
Code:
With FormName
     .Height = 300
     .Width = 300
End With

HTH
 

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
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
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
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
 

Stromma

Board Regular
Joined
Feb 8, 2004
Messages
240
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,100
Members
425,453
Latest member
bince

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
Top