Code not formatting correctly in textbox userform

[ipbr21054]

On my worksheet i have a table of customers with their values.

I have a useform of which the code is shown below.
My issue is TextBox4

I open the userform & enter a customers ID number & then the form is populated.
So in this example i enter 1 & i see the textboxes are populated.
I look at Textbox4 & i see £25.00

Lets say i now close the userform, on my worksheet i change the value of £25.00 to £20.00

Now i expect that when i open the userform & enter 1 again that Textbox4 should show £20.00 BUT i see 20

This pattern seems to be the same for any customer.
If i cgange the value on the worksheet then open the userform i only see example 35 as opposed £35.00 or 50 as oppose £50.00

Do you see wht the correct item isnt be displayed when userform is open.

Private Sub CustomerID_Change()
    Dim id As Variant, rowcount As Long, foundcell As Range
    Dim lRow As Long
    Dim i As Long
    
    On Error Resume Next
    id = CLng(CustomerID.Value)
    
    If Err.Number <> 0 Then
        id = 0
    End If
    On Error GoTo 0
    SpinButton1.Value = id

    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row    ' THIS IS COLUMN NUMBER WHERE CUSTOMER ID IS LOCATED


    With Worksheets("G INCOME").Range("M1:M" & rowcount)    ' THIS IS CELL REFERENCE OF WHERE THE TEXT CUSTOMER ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)
    
        If Not foundcell Is Nothing Then
            lRow = foundcell.Row
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = .Cells(lRow, i + 1)
            Next i

        Else

            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = vbNullString
            Next i
        End If
    End With

End Sub

 

[MARK858]

Then Excel is acting correctly, a format is just like putting a filter on a camera, it doesn't change what the original subject looks like.

Try...

        If Not foundcell Is Nothing Then
            lRow = foundcell.Row
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = Format(.Cells(lRow, i + 1), "£#,##0.00")
            Next i

I also 2nd what @Micron stated about learning to step through the code

 

[Micron]

Value in textbox after I enter 30 in formatted cell

Value after editing tb (remove 0 then leave textbox)

So BeforeUpdate formats the control.
EDIT - the linked code is no good if formatted to $ sign. I used Format(Me.TextBox1, "£#,##0.00")

 

[ipbr21054]

Then Excel is acting correctly, a format is just like putting a filter on a camera, it doesn't change what the original subject looks like.

Try...

        If Not foundcell Is Nothing Then
            lRow = foundcell.Row
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = Format(.Cells(lRow, i + 1), "£#,##0.00")
            Next i

I also 2nd what @Micron stated about learning to step through the code

This worked for me Many Thanks.

My question now is do i put the Textbox4 code just like this if the format you advised fixed it ?

    Private Sub TextBox4_AfterUpdate()
        TextBox4.Value = Format(TextBox4.Value, "£#,##0.00")
        TextBox4 = UCase(TextBox4)
    End Sub

FROM THE ABOVE

TO THE BELOW ?

    Private Sub TextBox4_Change()
        TextBox4 = UCase(TextBox4)
    End Sub

 

[Micron]

I don't see a need for most of that, especially that which I linked to as an example only. This is all I have, and it works, and since the control is linked to the cell, it doesn't have to be formatted either. In fact, it's probably best not to since the Int line will raise an error if you edit the control and leave the pound sign in there.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBox1 = Format(Me.TextBox1, "£#,##0.00")

End Sub

 

[ipbr21054]

Getting confused now.

Are you talking about the same this as @MARK858 OR SOMETHING DIFFERENT

 

[Micron]

OR SOMETHING DIFFERENT

All caps like that is shouting at me.
I'm going to bow out and eliminate confusion for you. I'm sure Mark858 will be able to move you forward, especially since you're going with his suggestion.
The result of the code I last posted can be visualized by looking at the pics I posted but I'll add one other test:
Assuming you're using the form as data entry (prior pics were about data edits):

Value from cell B7 (which is nothing) thus tb is empty:

Value in B7 and textbox after I enter 200 in tb and tab out of it:

If I wanted B7 to show currency, I would format it as you did.

 

[ipbr21054]

Sorry the caps stayed on from when I typed Mark

 

[ipbr21054]

@MARK858
Just noticed that this line of code also applied the currency format to TextBox5

TextBox4 should use the format code as you advised.

TextBox5 is mileage so just a normal value of 1,2,3 etc but its showing £3.00

Can you advise how the £ format is applied to TextBox4 only please.
Thannks

Me.Controls("TextBox" & i).Value = Format(.Cells(lRow, i + 1), "£#,##0.00")

 

[MARK858]

Ugly way (assuming it is only 4 you want in currency format as you mention 4 and 5 but not 1,2 and 3)...

        For i = 1 To 5
            If i = 4 Then
                Me.Controls("TextBox" & i).Value = Format(.Cells(lRow, i + 1), "£#,##0.00")
            Else
                Me.Controls("TextBox" & i).Value = .Cells(lRow, i + 1)
            End If
        Next i

 

[ipbr21054]

This works but should i use yours ?

   Me.Controls("TextBox" & i).Value = .Cells(lRow, i + 1)
                Me.Controls("TextBox4").Value = Format(.Cells(lRow, i + 1), "£#,##0.00")

1 Name
2 Address
3 Post Code
4 Charge hence £
5 Mileage covered hen 5,6,7 etc