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

[netuser]

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 :

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

 

[Jaafar Tribak]

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

What are ColIndex 1, ColIndex 2 and ColIndex 4 ? are they Textboxes ?

It would be helpful if you could upload a demo workbook so we can take a look .

 

[netuser]

Yes they all are textboxes just like others. But I searched a little bit and i was able to do it myself, I don't know if it is the best solution, you may comment and share if you have better one.



Code in userform2:

[COLOR=#ff0000]Private TextArray() As New Class2
[/COLOR]

Private Sub AddBox(row, colIndex)


    
    Dim box As MSForms.TextBox
  
       Static i As Long


    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 - 3) * padWidth + leftMargin
        .height = height
        .width = width
        .Top = (row.row - 1) * padHeight + topMargin
        .Value = row.Cells(1, colIndex).Value
    End With

    If colIndex = 4 Or colIndex = 5 Or colIndex = 6 Or colIndex = 7 Or colIndex = 8 Then

        If colIndex = 8 Then
        box.Locked = True
        box.Value = Format(box.Value, "#,##0.00")
        End If
        
        box.Value = Format(box.Value, "#,##0.00")
        ReDim Preserve arTextBoxes(i)
        Set arTextBoxes(i) = box: i = i + 1
        
[COLOR=#ff0000]        ReDim Preserve TextArray(1 To i)[/COLOR]
[COLOR=#ff0000]        Set TextArray(i).TxtEvents = box[/COLOR]
        
    End If
    
End Sub

I created a class module (Class2) :

Public WithEvents TxtEvents As MSForms.TextBox

Private Sub TxtEvents_Change()

Dim PartialName, Prime, Claims, Taxes, Others

PartialName = Left(TxtEvents.Name, 1)


On Error Resume Next
Prime = UserForm2.Controls(PartialName & 4).Value * 1
Claims = UserForm2.Controls(PartialName & 5).Value * 1
Taxes = UserForm2.Controls(PartialName & 6).Value * 1
Others = UserForm2.Controls(PartialName & 7).Value * 1

Net = Prime - (Claims + Taxes + Others)

UserForm2.Controls(PartialName & 8).Value = Format(Net, "#,##0.00")
End Sub

 

[Jaafar Tribak]

Yes they all are textboxes just like others. But I searched a little bit and i was able to do it myself, I don't know if it is the best solution, you may comment and share if you have better one.

Code in userform2:

[COLOR=#ff0000]Private TextArray() As New Class2
[/COLOR]

Private Sub AddBox(row, colIndex)


    
    Dim box As MSForms.TextBox
  
       Static i As Long


    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 - 3) * padWidth + leftMargin
        .height = height
        .width = width
        .Top = (row.row - 1) * padHeight + topMargin
        .Value = row.Cells(1, colIndex).Value
    End With

    If colIndex = 4 Or colIndex = 5 Or colIndex = 6 Or colIndex = 7 Or colIndex = 8 Then

        If colIndex = 8 Then
        box.Locked = True
        box.Value = Format(box.Value, "#,##0.00")
        End If
        
        box.Value = Format(box.Value, "#,##0.00")
        ReDim Preserve arTextBoxes(i)
        Set arTextBoxes(i) = box: i = i + 1
        
[COLOR=#ff0000]        ReDim Preserve TextArray(1 To i)[/COLOR]
[COLOR=#ff0000]        Set TextArray(i).TxtEvents = box[/COLOR]
        
    End If
    
End Sub

I created a class module (Class2) :

Public WithEvents TxtEvents As MSForms.TextBox

Private Sub TxtEvents_Change()

Dim PartialName, Prime, Claims, Taxes, Others

PartialName = Left(TxtEvents.Name, 1)


On Error Resume Next
Prime = UserForm2.Controls(PartialName & 4).Value * 1
Claims = UserForm2.Controls(PartialName & 5).Value * 1
Taxes = UserForm2.Controls(PartialName & 6).Value * 1
Others = UserForm2.Controls(PartialName & 7).Value * 1

Net = Prime - (Claims + Taxes + Others)

UserForm2.Controls(PartialName & 8).Value = Format(Net, "#,##0.00")
End Sub

Yes. The class event is the way to go for catching the textboxes change events as you did.

Adding the values in the textboxes will probably need some validation checking plus conversion.

If you could upload a workbook demo it would be easier for me to check.

 

[netuser]

Unfortunately there are other userforms and there is a lot of confidential data everywhere, it will take too much time and work to put dummy data

If you have any advise to how to implement the data validation, it will be great

Thanks,