How I implemented UserForm resizing

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
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:

form-height-1.gif


form-height-2.gif


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:


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:

VBA Code:
    SetFormHeight Me, ListBox1, "RecordTransferHeight"

In the form's Module:

VBA Code:
' ~~~~~~~~ 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:

VBA Code:
' ~~~~~~~~~~~~~~~~~~~~ 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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,127,905
Messages
5,627,568
Members
416,254
Latest member
Monicamicaela

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top