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 .

 

[MKLAQ]

@dmt32
awesome solution ! just question about format how can I show number format in column 4 on userform to show format number like this
-#,##0.00"

 

[EXCEL MAX]

With some changes according to post 14 you can make this replacements.
DMT32's code also works fine and that is enough to give the answer to the original post.
All that we made after that was just experimental game to teach you how it may works.
I'm glad that you were interested to listen and hope you will be able go further alone.

Option Explicit

Dim vFormHeader As Long, vFromBottom As Long, vLastRow As Long 'change
Dim vTCounter As Long, vNBox As Long, vN As Long
Dim vBox As Control, vRng As Range
Dim vR As Range, vRow2 As Range, vNRows As Long, vX As Long

Const cDateCol = 1
Const cInvoiceNumCol = 2
Const cAmountCol = 3
Const cPaymentCol = 4
Const cHeight = 15
Const cPadHeight = cHeight + 4
Const cTopMargin = 25
Const cBottom = 100
Const cFrmSpecialEffectBump = 6
Const cWidth = 50
Const cPadWidth = cWidth + 4
Const cLeftMargin = 5

Private Sub UserForm_Initialize() 'changed
    vFormHeader = Me.height - Me.InsideHeight
    vFromBottom = Me.InsideHeight - CommandButton1.Top
    vTCounter = 1
    With ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).row
        Set vRng = Range("A2:D" & vLastRow)
        Call FillFilteredRows
    End With
End Sub

Private Sub AddBox(vRow, vColIndex) 'changed
    vNBox = vNBox + 1
    Set vBox = Me.Controls.Add("Forms.TextBox.1", "MyTextBox" & vNBox)
    vBox.Left = (vColIndex - 1) * cPadWidth + cLeftMargin
    vBox.height = cHeight
    vBox.width = cWidth
    vBox.Top = (vRow - 2) * cPadHeight + cTopMargin
    vBox.Value = ActiveSheet.Cells(vRow, vColIndex).Value
End Sub

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

Private Sub CommandButton1_Click() 'changed
   Call FilterRows
End Sub

Sub ClearData() 'Changed
   For vN = 1 To vNBox
      Controls.Remove "MyTextBox" & vN
   Next vN
   vNBox = 0
End Sub

Sub FilterRows() 'changed
   Dim vCriteria
   vCriteria = InputBox("Insert or change criteria to show data " & _
      "in column 3 according to search criteria.", "Criteria Box", "<0")
   If Not vCriteria = "" Then
      Call ClearData
      Application.ScreenUpdating = False
      With ActiveSheet
         Set vRng = .Range("A1").Resize(vLastRow, 4)
         vRng.Columns(3).AutoFilter 1, vCriteria
         Call FillFilteredRows
         ActiveSheet.ShowAllData
         vRng.Columns(3).AutoFilter
      End With
      Application.ScreenUpdating = True
   End If
End Sub

Sub FillFilteredRows() 'Changed
      On Error GoTo EX
      Set vRng = ActiveSheet.Range("A2:D" & vLastRow).SpecialCells(xlVisible)
      For Each vR In vRng.Areas
         For Each vRow2 In vR.Rows
            vX = vNRows * 4
            vTCounter = vTCounter + 1
            Call AddBox(vTCounter, cDateCol)
            Controls("MyTextBox" & vX + 1) = vRow2.Cells(1)
            Call AddBox(vTCounter, cInvoiceNumCol)
            Controls("MyTextBox" & vX + 2) = vRow2.Cells(2)
            Call AddBox(vTCounter, cAmountCol)
            Controls("MyTextBox" & vX + 3) = vRow2.Cells(3)
            Call AddBox(vTCounter, cPaymentCol)
            Controls("MyTextBox" & vX + 4) = Format(vRow2.Cells(4), "-#,##0.00")
            vNRows = vNRows + 1
         Next vRow2
      Next vR
      Me.height = vTCounter * cPadHeight + cTopMargin + cBottom + vFormHeader
      CommandButton1.Top = Me.InsideHeight - vFromBottom
      vTCounter = 1
      vNRows = 0
      Exit Sub
EX:
End Sub

 

[dmt32]

@dmt32
awesome solution ! just question about format how can I show number format in column 4 on userform to show format number like this
-#,##0.00"

If the cell the textbox gets its data from is displaying the required format then all should need to do is use the Text property of the range

change this line

.Value = ActiveSheet.Cells(CellRow, i).Value

to this

.Value = ActiveSheet.Cells(CellRow, i).Text

Dave

 

[MKLAQ]

Dave . I don't expect TEXT show number formatting . I used to add line "-#,##0.00" as EXCEL MAX did it . for me this changing is impressive and I'm surprised TEXT does that .
thanks for your solution

 

[MKLAQ]

@EXCEL MAX
I appreciate your efforts to following for all of my requirements . indeed nobody spend much time for answer new requirements ,especially has answered OP
thanks for every thing

 

[EXCEL MAX]

 

[dmt32]

Dave . I don't expect TEXT show number formatting . I used to add line "-#,##0.00" as EXCEL MAX did it . for me this changing is impressive and I'm surprised TEXT does that .
thanks for your solution

Range.Text property (read only) returns what you see in the cell & not its underlying value so if for instance you have formatted a cell as currency showing the $, Text property will return $100,00 whereas Value property will return 100.00

Glad that we were able to assist in helping resolve your issue

Dave