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

 

[Micron]

Maybe something like this?

Format A User Form Text Box As Currency Or A Percentage

You are building a custom user form to calculate a monthly payment, based on loan amount, number of payments, and interest rate. You would like one text box on the form to be formatted as currency and another to be formatted as a percentage. There do not appear to be a properties to format the...

www.mrexcel.com

VBA code tags are better than Rich BB tags for posting code.

 

[ipbr21054]

I have looked at that page & now added it to my userform BUT the two customers in question STILL show 60 as opposed £60.00
The code on that page is in the format of a picture as opposed code of which you can copy so this is what i have.
I get no errors etc so i assume ive done it correctly.

Is this the correct VBA code tags you mention ?

    Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If Int(Me.TextBox4.Value) = TextBox4.Value Then
           Me.TextBox4.Value = Format(Me.TextBox4.Value, "$#,##0")
        Else
           Me.TextBox4.Value = Format(Me.TextBox4.Value, "$#,##0.00")
        End If
        TextBox4 = UCase(TextBox4)
    End Sub

 

[Micron]

Put a break point on your BeforeUpdate event and step through it (F8). My guess is that you'll find that the Change event runs afterwards and messes up the format. If that code runs after, you appear to be undoing things with this:
Me.Controls("TextBox" & i).Value = .Cells(lRow, i + 1

You need to remove anything in every event/sub that will run after formatting if those actions alter the value of the textbox or the cell it is linked to. Or you will need to do what you need to do in those events (e.g. Change event) and from one of those events, call a separate sub that does the same thing as the BeforeUpdate event. I don't suppose disabling events after the update event is really an option, but for future reference, sometimes you need to do that to prevent other code from running.

EDIT -but you would not also use the BeforeUpdate event code as well as calling a sub that does the same thing.

 

[ipbr21054]

Im never any good with stepping through it so i will have to leave that.
Can / shall i put a download file here for you to look at ?

What i dont understand is Why when i change the worksheet value from £20.00 to say £25.00 then open the form the form textbox just doesnt display whats on the page ?

 

[MARK858]

Are you typing in the cell £25.00 i.e, text, or are you typing in a number 25 or a number 25.00 and then the cell is being formatted to show £25.00?

 

[ipbr21054]

Looking at the what i see in the cell is as follows.
£20.00

I Select the £20.00 & type 22 & when i leave the cell i then see £22.00

Right clicking the cell the format is as follows.
Currency
Decimal places 2
It shows example of £22.00

I have supplied screenshot for you

 

[Micron]

You can only post links to file shares in these forums. I would take a look for you.
You really should investigate how to step through code if you're going to continue to work with it. That is basic troubleshooting 101. Perhaps you can find YouTube videos on the subject.

 

[MARK858]

So what are you actually inputting in the cell (not what you see)? by the fact you are using currency format I assume that you are only putting in a number and that is what Excel takes (the underlying value), not what it is formatted as.

 

[ipbr21054]

I enter 20 or 55 or 37 etc etc