VBA: UserForms: Manipulating through VBA

DrGuru · Aug 8th 2003

Good morning everyone,

Just a quick question here. This is somewhat related to a post that I had made on here some time ago relating to adding controls to a userform using VBA.

My question is this:

Is it possible to to code your form in such a way so that when activated, it will detect the users screen resolution and resize itself so that it is always say 80% of both the x and y axis? For example, is the user has their screen resolution set at 800X600, then the userform would resize itself to 640X480.

There is a second part to this query, but I will leave that for a little later. My thanks to anyone who is able offer any insight on this.

Richie(UK) · Aug 8th 2003

Hi DG,

Two options here:

1. Test for the screen resolution and have a different form for each resolution.

Something like this:

Code

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 UFByVideoInfo()
Dim vidWidth As Integer, vidHeight As Integer


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 = 800 And vidHeight = 600 Then
    frm800600.Show
Else
    frm640480.Show
End If


End Sub

Display More

2. Resize the form based upon the height and width of the application window.

Like this:

Code

Sub UFVariable()
    frmVariable.Show
End Sub


Private Sub UserForm_Initialize()
    Const sHgt As Single = 0.8
    Const sWdth As Single = 0.8
    Me.Height = Application.Height * sHgt
    Me.Width = Application.Width * sWdth
End Sub

Display More

HTH

Egad · Aug 8th 2003

This approach works for me on 800x600 but I haven't tested on other PCs & other resolutions.

Private Sub ResizeUserform()
Load UserForm1
UserForm1.Width = Application.Width * 0.8
UserForm1.Height = Application.Height * 0.8
UserForm1.StartUpPosition = 2 '(center on screen)
UserForm1.Show
End Sub

DrGuru · Aug 11th 2003

Good afternoon,

Sorry for me delayed reply. I suffered a system crash that took out my internet for the weekend.

Richie - I tried both your methods, but was only able to get the second one to work. Is the code in your first example specific to a particular version of windows or excel? I am running Windows XP Pro and Excel 2002 at home and at work, it is Windows 95 and Excel 2000. It is my home system that I attempted your first example with. Any suggestions?

Richie(UK) · Aug 11th 2003

Hi DG,

Sorry, I should have explained further. In the first example the forms "frm800600" and "frm640480" are forms that you have sized for the appropriate screen resolution. So, if the resolution is 800x600 you display the form that you have created for that resolution, otherwise you display the form that you created for the other resolution.

HTH

DrGuru · Aug 12th 2003

Hi Richie,

Yes, I understood that from your initial reply. Is there no way to just have one form that resizes itself based on the system screen resolution as opposed to being based on the application window parameters?

VBA: UserForms: Manipulating through VBA

Participate now!

Share