Maximize user form in Excel app. 4

[Numbers1]

I would like to maximize a user form that I have created for a project that I am developing. There does not appear to be a way to do this in either the properties window or in the upper right corner of the user form. The help instructions refer to minimize, maximize and close but provide no further information.
Can anyone help?
Thanks, Numbers

 

[Nelviticus]

I don't think it's possible. Which application are you working in? In Word it's possible to calculate the display height and width in points and then re-size your form to those dimensions (almost the same as maximising it), but I can't figure out a way of doing it in Excel.

If you're using Word, put the following in a module:

Option Explicit

Declare Function GetSystemMetrics32 Lib "User32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

Public Function GetScreenWidth() As Single
    
    GetScreenWidth = PixelsToPoints(GetSystemMetrics32(0), False)
    
End Function

Public Function GetScreenHeight() As Single
    
    GetScreenHeight = PixelsToPoints(GetSystemMetrics32(1), True)
    
End Function

Then put the following in the function you want to use to maximise your form (I used a command button on the form, but you could use the 'UserForm_Initialise' event or something else):

Private Sub CommandButton1_Click()
    
    Me.Left = 0
    Me.Top = 0
    Me.Height = GetScreenHeight
    Me.Width = GetScreenWidth
    
End Sub

Hope that helps.

Nelviticus

 

[TonyJollans]

Hi Numbers,

In Excel (or Word) you can use the properties of the Window to find out where to put, and how big to make, your userform, but there are no built-in sizing mechanics.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Numbers1]

Thanks for the help.
Numbers

 

[IvanMoala]

There are a number of ways to Max a Userform!
Here is one......using the MinMaxrestore Menu native to an App Window...

Worked on Excel2000 / WinXP

Option Explicit

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

Private Declare Function GetWindowLong _
    Lib "user32" _
        Alias "GetWindowLongA" ( _
            ByVal hWnd As Long, _
            ByVal nIndex As Long) _
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 Declare Function DrawMenuBar _
    Lib "user32" ( _
        ByVal hWnd As Long) _
As Long

Private Const GWL_STYLE As Long = (-16)
Private Const WS_SYSMENU As Long = &H80000
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000

Private Sub UserForm_Activate()
Dim Frmhdl As Long
Dim lStyle As Long

Frmhdl = FindWindow(vbNullString, Me.Caption)

lStyle = GetWindowLong(Frmhdl, GWL_STYLE)
lStyle = lStyle Or WS_SYSMENU
lStyle = lStyle Or WS_MINIMIZEBOX
lStyle = lStyle Or WS_MAXIMIZEBOX

SetWindowLong Frmhdl, GWL_STYLE, (lStyle)
DrawMenuBar Frmhdl

End Sub

Ivan F Moala

http://www.xcelfiles.com

 

[TonyJollans]

Hi Ivan,

Nice. Works in 97 too, but minimizing a modal form is a bit pointless.

Well worth a star from me.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[DrBowes]

Whats wrong with:

Application.WindowState = xlMaximized
UserForm1.Height = Application.Height
UserForm1.Width = Application.Width

Always useful to know API alternatives though.

 

[Nelviticus]

LOL, can't believe I went to all that trouble to find the API routines to grab the screen size and wrote functions to convert it to points when I could have just used Application.Height!

Nice and simple, thanks DrBowes!

Nelviticus

 

[IvanMoala]

Nothings wrong with

Whats wrong with:

Application.WindowState = xlMaximized
UserForm1.Height = Application.Height
UserForm1.Width = Application.Width

Which is why I said ONE way...

Ivan F Moala

http://www.xcelfiles.com

 

[Nelviticus]

Ivan, I thought your code was so useful that I've put up a FAQ about it, I hope you don't mind. I made it clear that all the credit is yours and I linked to this thread.

Nelviticus

 

[jajinder]

Hi, frehsman over here. Where do I put the code? In the class-mod?
What do you guys mean with:
Application.WindowState = xlMaximized
UserForm1.Height = Application.Height
UserForm1.Width = Application.Width
So, there is a shorter code?

Greets Jajinder

 

[ftoddt]

This is all really cool. I tried some of this on my userform and it works except that the whole form does not enlarge. I have a lot of windows and buttons and was hoping that all would enlarge proportionately. If I change my desktop screen resolution to 800 x 600 it looks perfect.
Perhaps that is why some programs state that they would look best at this or that resoulution. I assume that it cannot change to match each and every screen resolution.
Thanks for posting all this stuff. You guys are great.

 

[01Patrik]

ftoddt,

You can of course adjust the size of your form with IF-statements or select case on height/width. Same thing with font size etc:

Private Sub UserForm_Initialize()

Dim i As Integer, MySize As Variant

Application.WindowState = xlMaximized
UserForm1.Height = Application.Height
UserForm1.Width = Application.Width

MySize = Array(8, 14, 20)

Select Case UserForm1.Width

Case Is < 600
    i = 1
Case Is < 800
    i = 2
Case Else
    i = 3

End Select

CommandButton1.Font.Size = MySize(i)

End Sub

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.