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

N

netuser

Active Member
Joined
Jun 19, 2015
Messages
420
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 :)
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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 :)
 
Upvote 0
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 :)
Click to expand...

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.
 
Last edited:
Upvote 0
@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)
Click to expand...
:confused: :confused: :confused:

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
 
Upvote 0
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.
Click to expand...


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
 
Upvote 0
Yongle: for now i am using jaafar code
Click to expand...

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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
add procedure to userform to resize form based on addition text boxes
2 3
Replies
26
Views
2K
dmt32
D

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top