How I implemented UserForm resizing

[donwiss]

I initially looked on the web and found a fancy resizer that used a Class Module. I tried it, but it failed to run on my Excel 2002. But even had I gotten it to work, it would have resized in both dimensions. I only want the user to be able to lengthen and shorten the height of the form. So I stopped looking and created a simple one with up and down buttons. I ended up with two versions:

The buttons are labels with these properties:

.Caption = q or p
.Font = Wingdings 3
.Height = 13
.SpecialEffect = fmSpecialEffectRaised
.TextAlign = fmTextAlignCenter
.Width = 21 or 18

Optional:

.TabStop = True

I store all sorts of settings in the user's Registery. Sort order, form location, form height, etc. Long ago I got the code from:

Microsoft Support

Microsoft support is here to help you with Microsoft products. Find how-to articles, videos, and training for Microsoft Copilot, Microsoft 365, Windows, Surface, and more.

support.microsoft.com

But the link went dead. [If anyone can figure out the replacement, please post.] You can, of course, save all these settings in the WorkBook, but in my case the application has no WorkSheets, only lots of forms and Access tables.

Up top for the form height:

Private Const MinHeight As Single = 312.75

In the Initialize routine:

    SetFormHeight Me, ListBox1, "RecordTransferHeight"

In the form's Module:

' ~~~~~~~~ Form Length Changing ~~~~~~~~

Private Sub bnLengthen_Click()
    AdjustFormHeight 25
End Sub

Private Sub bnShorten_Click()
    AdjustFormHeight -25
End Sub

Private Sub AdjustFormHeight(Incr As Integer)
    AdjustHeightSub Incr, MinHeight, Me, ListBox1, "RecordTransferHeight"
    ListBox2.Height = ListBox1.Height
    frmForm.Top = Me.Height - 60
    lblDivider.Height = Me.Height - 264
    bnClose.Top = Me.Height - 53
End Sub

In a regular Module:

' ~~~~~~~~~~~~~~~~~~~~ adjusting form height ~~~~~~~~~~~~~~~~~~~~

Sub SetFormHeight(myForm As Object, myListBox As Control, KeyName As String)
' myListBox can be TextBox or iGrid
' if there is more than one ListBox, after this set their lengths to the first
    Dim H As String
    H = QueryKey("Software\BondCalc\Settings", KeyName)
    If H <> "" Then
        myListBox.Height = myListBox.Height + (H - myForm.Height)
        myForm.Height = H
    End If
End Sub

Sub AdjustHeightSub(Incr As Integer, MinHeight As Single, myForm As Object, myListBox As Control, KeyName As String)
    If Incr = 0 Then Exit Sub
    If myForm.Height + Incr < MinHeight Then
        Incr = MinHeight - myForm.Height
    End If
    myForm.Height = myForm.Height + Incr
    myListBox.Height = myListBox.Height + Incr
    SaveRegKeyValue KeyName, myForm.Height
End Sub

I did find one quirk when implementing this. I had named the frame and all the controls in it the same. When one form with them was over another form that also had these same named buttons, the buttons and frame caption on the under-form disappeared.

I'm open to comments.