Auto Size/Adjust Userform

debugcn 投稿 Dev

Richard Baluyut

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

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

Is there any way to do this?

Variatus

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.

You may have to hide the control again.
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.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集2021-06-12

コメントを追加

サインイン

分類Dev

Related 関連記事

記事

Auto Size/Adjust Userform

Auto Size/Adjust Userform

UserForm ComboBox

Update a userform listbox after userform initialization

VBA UserForm label in spreadsheet

Conflict bewteen OLEObjects and Userform

Put variable in UserForm name

sql source for a vba userform

Self Adjusting Userform Size

Userform not copying data to spreadsheet

Move value from one UserForm to another (variable) UserForm

Userform listbox that depends on another listbox

Userform QueryClose Will only Work Once

VBA - destroy a modeless UserForm instance properly

Is it possible to create and handle a custom Event in a Customized UserForm?

Userform Timestamp Cell When textbox Update

How to pass a property as an argument for userform controls?

Excel VBA UserForm printscreen save as pdf

Chosen result from Worksheet cell to Userform Combobox

Delete a selected row from list boxes in Userform

Use content of cell as text for label in UserForm

Vba UserForm combobox items don't apear till i click on the userform

UserForm変数スコープ：userform2からuserform1に2D配列値を転送します

VBA Entering userform data at next blank row correctly

Excel VBA Userform Listbox Dynamic Context Menu Using .OnAction Method

TextBox / Userformの日付値の比較

Using sql recordset to determine which userform is shown on loading

Double click event in the excel sheet column to fill all textboxes of a Userform

How to maintain cursor position when enforcing Change() on a UserForm TextBox?

How to create a Login in userform with 2 or 3 Username and Password?

How do I call a module from my userform in vba?