Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Maximising a userform when it inializes

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me how to make a userform maximize to the screen size regardless of the screen settings.

    Please help!!

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-02 04:46, Steve Jukes wrote:
    Can anyone tell me how to make a userform maximize to the screen size regardless of the screen settings.

    Please help!!
    Perhaps this will help
    This option places the Min/Max buttons on the
    Userform.....if you want it to Max all the time then look @ Option2


    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 Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

    Private Const GWL_STYLE As Long = (-16) 'Sets a new window style
    Private Const WS_SYSMENU As Long = &H80000 'Windows style
    Private Const WS_MINIMIZEBOX As Long = &H20000
    Private Const WS_MAXIMIZEBOX As Long = &H10000
    Private Const SW_SHOWMAXIMIZED = 3

    Private Sub UserForm_Activate()
    Dim lFormHandle As Long, lStyle As Long
    '===========================================
    '= Originally from Dax =
    '= Modified with comments by Ivan F Moala =
    '= 22/07/01 =
    '===========================================

    'Lets find the UserForm Handle the function below retrieves the handle
    'to the top-level window whose class name ("ThunderDFrame" for Excel)
    'and window name (me.caption or UserformName caption) match the specified strings.
    lFormHandle = FindWindow("ThunderDFrame", Me.Caption)

    'The GetWindowLong function retrieves information about the specified window.
    'The function also retrieves the 32-bit (long) value at the specified offset
    'into the extra window memory of a window.
    lStyle = GetWindowLong(lFormHandle, GWL_STYLE)

    'lStyle is the New window style so lets set it up with the following
    lStyle = lStyle Or WS_SYSMENU 'SystemMenu
    lStyle = lStyle Or WS_MINIMIZEBOX 'With MinimizeBox
    lStyle = lStyle Or WS_MAXIMIZEBOX 'and MaximizeBox

    'Now lets set up our New window the SetWindowLong function changes
    'the attributes of the specified window , given as lFormHandle,
    'GWL_STYLE = New windows style, and our Newly defined style = lStyle
    SetWindowLong lFormHandle, GWL_STYLE, (lStyle)

    'Remove >'< if you want to show form Maximised
    'ShowWindow lFormHandle, SW_SHOWMAXIMIZED 'Shows Form Maximized

    'The DrawMenuBar function redraws the menu bar of the specified window.
    'We need this as we have changed the menu bar after Windows has created it.
    'All we need is the Handle.
    DrawMenuBar lFormHandle

    End Sub


    OPTION2


    Option Explicit

    Private Const SM_CYSCREEN As Long = 1
    Private Const SM_CXSCREEN As Long = 0
    Const SM_CXFULLSCREEN = 16
    Const SM_CYFULLSCREEN = 17
    Const SM_CXMINTRACK = 34
    Const SM_CYMINTRACK = 35
    Const SM_CXMAXIMIZED = 61
    Const SM_CYMAXIMIZED = 62
    Private Declare Function GetSystemMetrics Lib "user32" _
    (ByVal nIndex As Long) As Long


    Private Sub UserForm_Activate()
    Dim lScreenHeight As Long, lScreenWidth As Long
    Dim lwinwidth, lWinHeight
    lScreenHeight = GetSystemMetrics(SM_CYMAXIMIZED) '(SM_CYSCREEN)
    lScreenWidth = GetSystemMetrics(SM_CXMAXIMIZED) '(SM_CXSCREEN)
    'lwinwidth = ActiveWindow.PointsToScreenPixelsX(lScreenWidth)
    'lWinHeight = ActiveWindow.PointsToScreenPixelsY(lScreenHeight)

    Me.Left = 0
    Me.Top = 0
    Me.Height = lScreenHeight ' - 200
    Me.Width = lScreenWidth 'lwinwidth 'lScreenWidth
    End Sub






    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan thank you the 1st option works fine if you remove the (') symbol were you say so, so the form maximses on being activated.
    I found the 2nd option opened the form to big and therefore i needed to divide the scrrenwidth by 1.35 setting and screenheight setting by by 1.40 for it to reduce to screen size.

    Other than that it worked fine.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    529
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan - Option 2 worked for me. Thanks! Is there a way to make the controls on the form re-position based on the users screen size?

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this in the Form module:


    Private Sub UserForm_Initialize()
    With Application
    Me.Top = .Top
    Me.Left = .Left
    Me.Height = .Height
    Me.Width = .Width
    End With
    End Sub


    The code leaves the taskbar and menubar displayed (on my PC).

    Regards,

    Mike


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    529
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ekim - Are you saying that code will re-position the controls on a UserForm based on the size of the users screen? It didn't work for me. ?

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Roger C
    I was altered to your posts as when I set the original question I asked the board to notify me of any replies.
    I have now, using Ivan cde set a form up with various controls and made it resize depending on the users screen setting it will also resize and reposition all controls depending on the new sise of the userform.
    I hope this code helps, I must admit that I took some from other posts so this is not all my work!!!

    This code is placed in the userform initialise section.
    TextBox1 = Me.Width
    TextBox2 = Me.Height

    This code is placed in the userform activate section.

    Dim ctl As Object
    Dim i As Integer
    FW = Val(Userform1.TextBox1)'This takes the width of the user before it maximises
    FH = Val(userform1.TextBox2)''This takes the hieght of the user before it maximises
    nfw = userform1.Width' This is the userforms new width
    nfh = userform1.Height' This the userforms new height

    On Error Resume Next
    For Each ctl In userform1.Controls
    If ctl.Left > 0 Then ctl.Left = (((ctl.Left)) * (nfw / FW))
    If ctl.Top > 0 Then ctl.Top = (ctl.Top * (nfh / FH))

    ctl.Width = (ctl.Width * (nfw / FW))


    'ctl.Width = (ctl.Width * (nfw / FW))
    ctl.Height = (ctl.Height * (nfh / FW))

    ctl.Font.Name = "Arial"

    If TypeName(ctl) = "ListBox" Then

    ctl.Font.Size = ctl.Font.Size * ((nfh / FH)) - 2
    Else

    ctl.Font.Size = ctl.Font.Size * ((nfh / FH))
    End If

    Next ctl

    I hope this helps.

    Please let me know how you egt on!!


    Steve

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    529
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Steve - I put the initialise code into a Private Sub UserForm_Initialize() and the other code in the UserForm_Activate Sub, but when I run it I get a 'Compile Error: Variable Not Defined on TextBox1'. Any idea what I may have done wrong?

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    RogerC,

    Sorry for not getting back to you ealier but I have been out of the office for a few days.

    The reason for your error is that you have to have a textbox named textbox1 and another textbox named textbox2. The 2 textboxes are there to record the height and width of the form before it maximises, ie the height and width it is when your are working on it in vb editor.

    Hope this helps,

    Steve

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •