CODEWITHSUNDEEP
excelvbauserform
user12366709
user12366709

Reputation:

Auto Size/Adjust Userform

I'm new with VBA Programming, I have a Userform with TextBoxes,Labels, and Checkboxes.

My problem is how to Auto size or adjust the form, Because I have hidden Textbox and Label, Now if I click the Button it will unhide the Texbox and Label and also it will auto size the Form and the other controls.

I don't know how to start this.

Actual Result

enter image description here

Let's assume there is hidden Label and Texbox between Team Name and Last Name now I click the update RadioButton it will unhide the hidden Label and Texbox and adjust the form and other controls.

Expected Result

enter image description here

Is there any way to do this?

Upvotes: 1

Views: 991

Answers (1)

Variatus
Variatus

Reputation: 14373

Say, the space required by the control Tbx hidden at the level 90pt from the top is 30pt. Therefore all controls below it should be moved down by 30pt when Tbx is made visible, and the form's height also increased by that same measure. The code below would accomplish that while at the same time unhiding the control.

Dim Ctl As MSForms.Controls

For Each Ctl In Me.Controls
    With Ctl
        If .Top >= 90 Then
            If .Visible = True Then
                .Top = Top + 30
            Else
                .Visible = True
            End If
        End If
    End With
Next Ctl
Me.Top = Top + 30

Two things would be different in practice.

  1. You may have to hide the control again.
  2. Your would create the control on the fly rather than keep it hidden.

If you plan on hiding the control again you would need an array of all affected control names and another array (or dimension) for their existing Tops. You would loop through all the names in the array and reset the top, like, 

Me.Controls(Arr(0, i)).Top = Arr(1, i) + Iif(HideMe, 0, 30)

Look into creating the control on the fly. You should be able to simply create a copy of an existing control from which it inherits all properties. Any event code for that control needs to be prepared in advance, however.

Upvotes: 1

Related Questions