Search code examples
excellayoutuserformvba

VBA UserForm visible range


I have a userform with the dimensions (h*w) 180 * 240. To this userform controls are to be added by the UserForm_Initialize() event based on a myriad of user inputs after which the userform is to be resized . The problem I'm running into is the fact that the visible range of the userform is smaller than the actual range. To demonstrate I've inserted a second userform with the following code:

Private Sub UserForm_Initialize()
    Dim ctrl As Control
    Dim i As Integer

    For i = 1 To 2
        Set ctrl = Me.Controls.Add("Forms.Label.1")
        With ctrl
            Debug.Print .Name
            .Caption = i
            .BorderStyle = 1
            .Height = 10
            .Width = 10
        End With
    Next i

    Set ctrl = Me.Controls("Label1")
    With ctrl
        .Top = 0
        .Left = 0
    End With

    Set ctrl = Me.Controls("Label2")
    With ctrl
        .Top = Me.Height - .Height
        .Left = Me.Width - .Width
    End With
End Sub

which generates the following userform: USerform2 example 1

The first label sits exactly on the top and left edge of the userform but the second label is nowhere to be seen as it isn't on the visible part of the userform.

How do I get the second label to sit exactly on the bottom and right edge of the visible part of the userform like in the image below? (I've edited the first image to show what I want) enter image description here


Solution

  • Use the InsideHeight and InsideWidth properties:

    With ctrl
        .Top = Me.InsideHeight - .Height
        .Left = Me.InsideWidth - .Width
    End With