add procedure to userform to resize form based on addition text boxes

[MKLAQ]

Hi experts,
I hope somebody provide me procedure to resize the userform based on add textboxes automatically
the code will add textboxes based on data in sheet . the data in sheet contain four columns so it will add three columns on userform and the rows on userform depends on the last row in sheet.
is there any procedure to add the userform when run it?
should resize the userform by increase or decrease based on how many added textboxes on form?
this is the code

Option Explicit

Const dateCol = 1
Const invoiceNumCol = 2
Const amountCol = 3
Const paymentCol = 4

Private Sub SaveButton_Click()
    Dim row As Range
    Dim box As Control

    For Each row In ActiveSheet.Rows
        On Error GoTo ExitHandler
        row.Cells(1, paymentCol).Value = Me.Controls(row.row & paymentCol).Value
    Next row

ExitHandler:
    Exit Sub
End Sub



Private Sub UserForm_Initialize()
    Dim row As Range

    For Each row In ActiveSheet.Rows
     
        If row.Cells(1, dateCol).Value = "" Then
       
            Exit For
        End If
        'End If

        Call AddBox(row, dateCol)
        Call AddBox(row, invoiceNumCol)
        Call AddBox(row, amountCol)
        Call AddBox(row, paymentCol)
    Next row
End Sub

Private Sub AddBox(row, colIndex)
    Dim box As Control

    Const width = 50
    Const padWidth = width + 4
    Const height = 15
    Const padHeight = height + 4
    Const topMargin = 25
    Const leftMargin = 5
    Const frmspecialeffectbump = 6
    Set box = Me.Controls.Add("Forms.TextBox.1", row.row & colIndex)
    box.Left = (colIndex - 1) * padWidth + leftMargin
    box.height = height
    box.width = width
    box.Top = (row.row - 1) * padHeight + topMargin
    box.Value = row.Cells(1, colIndex).Value
End Sub

I hope somebody help .

 

[EXCEL MAX]

Hello MKLAQ,
If you want the to get needed height of the userform,
you need always to have number of the added textboxes rows.
So when you add new row increase variable counter by one.
It's good to some variables be declared outside of procedures.
Now you need to multiply this variable by verticaly distance between of two textboxes and
add distance of top margin multiplyed by 2(Don't ask me why).
If you want you can add the bottom also.
Hope this will be helpful.

Option Explicit

Const dateCol = 1
Const invoiceNumCol = 2
Const amountCol = 3
Const paymentCol = 4

Const height = 15
Const padHeight = height + 4
Const topMargin = 25
Const vBottom = 50
Dim vTCounter As Long

Private Sub UserForm_Initialize()

    Dim row As Range
    For Each row In ActiveSheet.Rows
        If row.Cells(1, dateCol).Value = "" Then
            Exit For
        End If
        Call AddBox(row, dateCol)
        Call AddBox(row, invoiceNumCol)
        Call AddBox(row, amountCol)
        Call AddBox(row, paymentCol)
        vTCounter = vTCounter + 1
    Next row
    Me.height = vTCounter * padHeight + 2 * topMargin + vBottom

End Sub

Private Sub AddBox(row, colIndex)

    Dim box As Control
    Const width = 50
    Const padWidth = width + 4
    Const leftMargin = 5
    Const frmspecialeffectbump = 6
   
    Set box = Me.Controls.Add("Forms.TextBox.1", row.row & colIndex)
    box.Left = (colIndex - 1) * padWidth + leftMargin
    box.height = height
    box.width = width
    box.Top = (row.row - 1) * padHeight + topMargin
    box.Value = row.Cells(1, colIndex).Value
   
End Sub

 

[MKLAQ]

nice works !

if you have free time just I would fix some problems I try doing loop from row 2 , but doesn't work if I change to this

 If row.Cells(2, dateCol).Value = "" Then

and I have commandbutton1 , should keep textboxes below

last thing how can I show the style borders like frmspecialeffectbump = 6 , font , textalign for textboxes
thanks again

 

[EXCEL MAX]

Thanks for feedback.
After editing I've got this.
Hope so that you will see the changes.

Option Explicit

Dim vTCounter As Long
Dim vLastRow As Long, row As Long

Const dateCol = 1
Const invoiceNumCol = 2
Const amountCol = 3
Const paymentCol = 4
Const height = 15
Const padHeight = height + 4
Const topMargin = 25
Const vBottom = 50

Private Sub UserForm_Initialize()

    With ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).row
        For row = 2 To vLastRow
            If .Cells(row, dateCol).Value = "" Then
                Exit For
            End If
            Call AddBox(row, dateCol)
            Call AddBox(row, invoiceNumCol)
            Call AddBox(row, amountCol)
            Call AddBox(row, paymentCol)
            vTCounter = vTCounter + 1
        Next row
    End With
    Me.height = vTCounter * padHeight + 2 * topMargin + vBottom

End Sub

Private Sub AddBox(row, colIndex)

    Dim box As Control
    Const width = 50
    Const padWidth = width + 4
    Const leftMargin = 5
    Const frmspecialeffectbump = 6
   
    Set box = Me.Controls.Add("Forms.TextBox.1", row & colIndex)
    box.Left = (colIndex - 1) * padWidth + leftMargin
    box.height = height
    box.width = width
    box.Top = (row - 1) * padHeight + topMargin
    box.Value = ActiveSheet.Cells(row, colIndex).Value
   
End Sub

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    
    With Control
        .SpecialEffect = 6
        .Font.Name = "Britannic"
        .TextAlign = 2
        .Font.Size = 8
    End With

End Sub

 

[MKLAQ]

thanks again . every thing is ok except the commandbutton when add textboxes it will cover over the commandbutton how can I fix it,please?

 

[EXCEL MAX]

First, my apologies.
I have saw calculation error.
I was forgot that userform have inside height and width.
Here's improved code, but I'm not shure where is the button and where you soppose to be after adding textboxes.
Here is example if you want to keep button on the bottom.

Option Explicit

Dim vTCounter As Long
Dim vLastRow As Long, row As Long, vFormHeader As Long

Const dateCol = 1
Const invoiceNumCol = 2
Const amountCol = 3
Const paymentCol = 4
Const vHeight = 15
Const padHeight = vHeight + 4
Const topMargin = 25
Const vBottom = 100
Const frmspecialeffectbump = 0
Dim vFromBottom  As Long

Private Sub UserForm_Initialize()
      
    vFormHeader = Me.height - Me.InsideHeight
    vFromBottom = Me.InsideHeight - CommandButton1.Top - CommandButton1.height
    With ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).row
        For row = 2 To vLastRow
            If .Cells(row, dateCol).Value = "" Then
                Exit For
            End If
            Call AddBox(row, dateCol)
            Call AddBox(row, invoiceNumCol)
            Call AddBox(row, amountCol)
            Call AddBox(row, paymentCol)
            vTCounter = vTCounter + 1
        Next row
    End With
    Me.height = vTCounter * padHeight + topMargin + vBottom + vFormHeader
    CommandButton1.Top = Me.InsideHeight - CommandButton1.height - vFromBottom
    
End Sub

Private Sub AddBox(row, colIndex)

    Dim box As Control
    Const width = 50
    Const padWidth = width + 4
    Const leftMargin = 5
   
    Set box = Me.Controls.Add("Forms.TextBox.1", "MyTextBox" & row - 1)
    box.Left = (colIndex - 1) * padWidth + leftMargin
    box.height = vHeight
    box.width = width
    box.Top = (row - 2) * padHeight + topMargin
    box.Value = ActiveSheet.Cells(row, colIndex).Value
   
End Sub

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    
    With Control
        .SpecialEffect = frmspecialeffectbump
        .Font.Name = "Britannic"
        .TextAlign = 2
        .Font.Size = 8
    End With

End Sub

 

[MKLAQ]

if you want to keep button on the bottom.

yes that's correct
gives compile error variable not defined in this CommandButton1 for this line

CommandButton1.height

 

[EXCEL MAX]

Give a name "CommandButton1" to your button on the form (default name),
or change this name in the code according to the name of command button on the Userform.

 

[MKLAQ]

Give a name "CommandButton1" to your button on the form (default name),

sorry my bad ! about button name is not matched with code .
well done buddy! every thing works perfectly
much appreciated for your help & time

 

[EXCEL MAX]