Userform Dynamic created multiple textboxes, change number format after update of value

netuser · Aug 2, 2018

Hi,

I created multiple Textboxes in a UserForm2 based on a row and values from a worksheet.

When a user modify a value in those boxes the format of number is not kept.

I would like to keep the number format when user change the value of some of these textboxes but I really dont know how to capture that event and change the format back to (Format(box.Value, "#,##0.00") .

Here is my code of UserForm2 of how the textboxes are created and value assigned and formating :

Code:

Option Explicit


Const CCie = 1
Const PrimeAmt = 2
Const NetAmt = 3
Const StateID = 4




Private Sub UserForm_Initialize()


    Dim row As Range


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


        Call AddBox(row, CCie)
        Call AddBox(row, PrimeAmt)
        Call AddBox(row, NetAmt)
        Call AddBox(row, StateID)
                
    Next row
        
End Sub


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


    Const width = 60
    Const padWidth = width + 4
    Const height = 15
    Const padHeight = height + 4
    Const topMargin = 80
    Const leftMargin = 5
    
    
    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
    
    If colIndex = 2 Or colIndex = 3  Then
    box.Value = Format(box.Value, "#,##0.00")
    End If
        
End Sub

Thanks for your help

Yongle · Aug 4, 2018

The code that you need is something like this..

Trigger when exiting new textbox

Code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox1
        .Text = Format(.Text, "#,##0.00")
    End With
End Sub

BUT the problem is that you are creating the textbox "on the fly" you do not want a procedure for each text box

A simple approach would be to loop all textboxes like this. Add this to your userform code and ALL textboxes are formatted when you click on the form outside any object

Code:

Private Sub UserForm_Click()
   For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            With ctrl
                .Text = Format(.Text, "#,##0.00")
            End With
        End If
    Next ctrl
End Sub

BUT you probably do not want every textbox formatted like that so find a way to either exclude the ones that you do not want or to only include the ones that you do want (those with col index 2 and 3)

Suggested solution
Create an array of the box names when you are formatting the boxes in columns 2 and 3
Then loop through that array inside your trigger event

Jaafar Tribak · Aug 4, 2018

The textbox event you want is the AfetrUpdate or Exit events .. Unfortunately, becuase you are adding the textboxes at runtime you cannot sink those events even if you were to use a generic Class module.

There is an API workaround that uses the ConnectToConnectionPoint API but it is rather complicated to set up .

Alternatively, you could use the following trick : (Changes\Additions to your code are in red)

Code:

Option Explicit

Const CCie = 1
Const PrimeAmt = 2
Const NetAmt = 3
Const StateID = 4

[COLOR=#ff0000][B]Private arTextBoxes() As Control
Private bClosing As Boolean[/B][/COLOR]

[B][COLOR=#ff0000]Private Sub UserForm_Activate()[/COLOR][/B]

   [B][COLOR=#006400] 'RUN ANY EXISTING CODE HERE BEFORE THE LOOP !!!![/COLOR][/B]
    
   [B][COLOR=#ff0000] Dim oPrevTextBox As Control
    On Error Resume Next
    
    bClosing = False
    Do
        If Not ActiveControl Is oPrevTextBox And _
        IsError(Application.Match(oPrevTextBox, arTextBoxes, 0)) = 0 Then
            oPrevTextBox.Value = Format(oPrevTextBox.Value, "#,##0.00")
        End If
        
        Set oPrevTextBox = ActiveControl
        DoEvents
    Loop Until bClosing
End Sub[/COLOR][/B]

[B][COLOR=#ff0000]Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bClosing = True
End Sub[/COLOR][/B]


Private Sub UserForm_Initialize()
    Dim row As Range
    For Each row In ActiveSheet.Rows
        If row.Cells(1, CCie).Value = "" Then
            Exit For
        End If
        Call AddBox(row, CCie)
        Call AddBox(row, PrimeAmt)
        Call AddBox(row, NetAmt)
        Call AddBox(row, StateID)
    Next row
End Sub

Private Sub AddBox(row, colIndex)
  
    Dim box As MSForms.Control
    [B][COLOR=#ff0000]Static i As Long[/COLOR][/B]

    Const width = 60
    Const padWidth = width + 4
    Const height = 15
    Const padHeight = height + 4
    Const topMargin = 80
    Const leftMargin = 5
    
    
    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
    
    If colIndex = 2 Or colIndex = 3 Then
        box.Value = Format(box.Value, "#,##0.00")
        [COLOR=#ff0000][B]ReDim Preserve arTextBoxes(i)[/B][/COLOR]
        [B][COLOR=#ff0000]Set arTextBoxes(i) = box: i = i + 1[/COLOR][/B]
    End If
        
End Sub

Yongle · Aug 5, 2018

Simpler than my earlier suggestion, add this to your userform code

Code:

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim c As Integer, n As Integer
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            On Error Resume Next
            n = ctrl.Name
            c = Right(n, 1)
            If c = 2 Or c = 3 Then ctrl.Text = Format(ctrl.Text, "#,##0.00")
            End If
    Next ctrl
End Sub

Explanation
- standard textbox names cannot be "numeric only"
- your code auto-creates textboxes with "numeric only" names "RowNo" folllowed by" Column No"
- above code filters for textbox names that are "numeric only" AND end in "2" or "3"

Suggested amendments to your code
Your code now does not require box formatting - above code formats when mouse is moved
"With" statement inserted to avoid repeating "box"

Code:

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

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

    [COLOR=#ff0000]'box formatting removed from here[/COLOR]
End Sub

This segment of code can be tidied up with a loop

Code:

Private Sub UserForm_Initialize()
    Dim row As Range, c As Integer
    For Each row In ActiveSheet.Rows
        If row.Cells(1, 1).Value = "" Then
            Exit For
        End If
[COLOR=#ff0000]        For c = 1 To 4
            Call AddBox(row, c)
        Next c[/COLOR]
    Next row
End Sub

netuser · Aug 6, 2018

Thank you so much both of you

@Yongle I first tried your solution but I got compilation error in UserForm_MouseMove and ctrl line was highlited (For Each ctrl In Me.Controls)

@Jaafar Tribak You solution is working perfectly

thanks so much, Just an other question If in NetAmt (colIndex 8) box I want to do the calculation: colIndex 2+colIndex 3+colIndex 5 - colIndex 4 and also lock the NetAmt box so users can not modify the numbers. Is it possible ?

(obviously i have more boxes but for simplicity i had initially removed them from code before posting )

Thanks

Jaafar Tribak · Aug 7, 2018

netuser said:
Thank you so much both of you

@Yongle I first tried your solution but I got compilation error in UserForm_MouseMove and ctrl line was highlited (For Each ctrl In Me.Controls)

@Jaafar Tribak You solution is working perfectly thanks so much, Just an other question If in NetAmt (colIndex 8) box I want to do the calculation: colIndex 2+colIndex 3+colIndex 5 - colIndex 4 and also lock the NetAmt box so users can not modify the numbers. Is it possible ?

(obviously i have more boxes but for simplicity i had initially removed them from code before posting )

Thanks

Based on your code, NetAmt corresponds to (ColIndex 3) not 8 .. so i am not sure I understand.

In theory, you could do the calculations by sinking the textboxes change or KeyUp events in a class module and you can lock the NetAmt boxes via setting the Locked Property to TRUE when first creatinng them.

Yongle · Aug 7, 2018

@Yongle I first tried your solution but I got compilation error in UserForm_MouseMove and ctrl line was highlited (For Each ctrl In Me.Controls)

I am guessing there is something else in your code that is causing your problem
I retested it just now by creating a new workbook, adding a userform, pasting the code, adding data in columns A1 to D10
The code runs without error and correctly formats the textboxes

Code:

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim c As Integer, n As Integer
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            On Error Resume Next
            n = ctrl.Name
            c = Right(n, 1)
            If c = 2 Or c = 3 Then ctrl.Text = Format(ctrl.Text, "#,##0.00")
            End If
    Next ctrl
End Sub

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

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

End Sub

Private Sub UserForm_Initialize()
    Dim row As Range, c As Integer
    For Each row In ActiveSheet.Rows
        If row.Cells(1, 1).Value = "" Then
            Exit For
        End If
        For c = 1 To 4
            Call AddBox(row, c)
        Next c
    Next row
End Sub

netuser · Aug 7, 2018

Jaafar Tribak said:
Based on your code, NetAmt corresponds to (ColIndex 3) not 8 .. so i am not sure I understand.

In theory, you could do the calculations by sinking the textboxes change or KeyUp events in a class module and you can lock the NetAmt boxes via setting the Locked Property to TRUE when first creatinng them.

I was able to Lock the textbox. For Calculation i really don't know how to do it.

Could you please post a example code ? Lets say i want to do ColIndex 1 + ColIndex 2 - ColIndex 4 .

Thanks for your help

Yongle: for now i am using jaafar code, if i need to change i will quote you

Thanks so much for your help

Yongle · Aug 7, 2018

Yongle: for now i am using jaafar code

If OP tells me that my code does not work I always check in case I made a mistake
This thread will be read by others later and it is important that errors are corrected

Use whichever code works best for you and make sure you understand it

netuser · Aug 8, 2018

Jaafar Tribak said:
.

Sorry to bump this so quickly, I just need this last part of calculation to finalize the project. If you could please help with it.

Thanks

Userform Dynamic created multiple textboxes, change number format after update of value

netuser

Active Member

Excel Facts

Yongle

Well-known Member

Jaafar Tribak

Well-known Member

Yongle

Well-known Member

netuser

Active Member

Jaafar Tribak

Well-known Member

Yongle

Well-known Member

netuser

Active Member

Yongle

Well-known Member

netuser

Active Member

Similar threads

Forum statistics

Share this page

Userform Dynamic created multiple textboxes, change number format after update of value

Active Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock