Userform doesnt save 1 field entry correctly

[ipbr21054]

Hi,

Ive been looking at a userform today & noticed 2 things.

Ist thing.
When the userform is open and i make an edit to the text within the fields i naturally click on SAVE CHANGES FOR THIS CUSTOMER.
I see the saved confirmation message & i am then taken back to the database.
Ive noticed that all changes made on the userform reflect correctly on the database APART from the field labelled PAID which is column O on the database,it put the value in that cell as £0.00

Example.
Database shows paid £50.00,i open up the userform and i see say £50.00 so i then change it to £60.00,save etc & back to database and i see £0.00

2nd thing.
Similar to the above but font size.
Userform field labeled Invoice Number,on database it is column P

The font size should be 16 but it saves it as 11

I believe i have supplied the correct part of the code if you could be so kind.
Thanks very much.

[/COLOR]Private Sub UpdateRecord_Click()

Dim C As Range
Dim i As Integer
Dim Msg As String
If Not IsComplete(Form:=Me) Then Exit Sub
Dim IsNewCustomer As Boolean
'New Part
 Dim ctrl As MSForms.Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then ctrl.BackColor = RGB(255, 255, 255)
    Next ctrl
'End New part


    If Me.NewRecord.Caption = "CANCEL" Then
        With Sheets("DATABASE")
            Set C = .Range("A:A").Find(What:=txtCustomer.Value, _
                                After:=.Range("A5"), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
        End With
        If Not C Is Nothing Then
              MsgBox "Customer already Exists, file did not update"
              Cells(C.Row, "Q").Value = TextBox1 '<<<<<<<<<<<<<<
              Exit Sub
        End If
    End If
    
    IsNewCustomer = CBool(Me.UpdateRecord.Tag)
       
    Msg = "CHANGES SAVED SUCCESSFULLY"
        
    If IsNewCustomer Then
    'New record - check all fields entered
        r = startRow
        Msg = "NEW CUSTOMER SAVED TO DATABASE"
        ws.Range("A6").EntireRow.Insert
        ResetButtons Not IsNewCustomer
        Me.NextRecord.Enabled = True
    End If
    
        On Error GoTo myerror
    Application.EnableEvents = False
    'Add / Update Record
    For i = 1 To UBound(ControlNames)
                        With Me.Controls(ControlNames(i))
            'check if date value
            If IsDate(.Text) Then
                ws.Cells(r, i).Value = DateValue(.Text)
            ElseIf i = 15 Then
            If .Text = "" Then
                     MsgBox .Name & " is empty!"
                     Exit Sub
                End If
                ws.Cells(r, i).Value = Val(.Text)
            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With


    Next i
    
    If IsNewCustomer Then
        Call ComboBoxCustomersNames_Update
        Range("A6:Q6").Interior.ColorIndex = 6
        
        With Sheets("DATABASE")
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
                .Range("A5:Q" & x).Sort Key1:=.Range("A6"), Order1:=xlAscending, Header:=xlGuess
                Range("A6:Q6").Borders.LineStyle = xlContinuous
                Range("A6:Q6").Borders.Weight = xlThin
            
       End With
              
    End If
    
    ThisWorkbook.Save
    
    'tell user what happened
    MsgBox Msg, 48, Msg
    
    Set C = Nothing
    
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
Unload Me



End Sub[COLOR=#333333]

 

[ipbr21054]

dmt32 wrote

Hi,

- If you enter any non numeric symbol in the textbox like £ sign then Val function will return 0

- Font.Size is set at 11 in the code

Dave

The £ sign is being added automatically as if i just enter say 23.45 it then shows £23.45

I see the code where its set at size 11 font.
Can we edit this so column P is size 16 font where the others are all size 11 font.

Now looking for where this £ is located.

Thanks

 

[ipbr21054]

Just an update.
On the userform in the cell where i enter the price paid say £56.77
If i remove the currency format for that cell then the userform edits / saves fine.

Only thing is that the database now shows 56.77 24.36 without any £ sign.

Surely there must be a away that the database can have a currency formatted cell but not to have it on the userform ?

Thanks

 

[Norie]

You shouldn't include currency symbols in a textbox, use a label if you need to.

As for what the value appears like on the sheet that's easily fixed by formatting the destination cell(s).

For example.

With ws.Cells(r, i)
    .Value = Val(.Text)
    .NumberFormat = "£#,##0.00"
End With

 

[ipbr21054]

Afternoon Norie,

Part of my code is supplied,
How would i add that to my existing code as if i put it like ws.Cells(r, i).Value = Val(.Text).NumberFormat = "£#,##0.00" then i see a run time error & = Val is shown as unqualifier

        On Error GoTo myerror    Application.EnableEvents = False
    'Add / Update Record
    For i = 1 To UBound(ControlNames)
                        With Me.Controls(ControlNames(i))
            'check if date value
            If IsDate(.Text) Then
                ws.Cells(r, i).Value = DateValue(.Text)
            ElseIf i = 15 Then
            If .Text = "" Then
                     MsgBox .Name & " is empty!"
                     Exit Sub
                End If
                ws.Cells(r, i).Value = Val(.Text)
            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With


    Next i

 

[ipbr21054]

You shouldn't include currency symbols in a textbox, use a label if you need to.

As for what the value appears like on the sheet that's easily fixed by formatting the destination cell(s).

For example.

With ws.Cells(r, i)
    .Value = Val(.Text)
    .NumberFormat = "£#,##0.00"
End With

I think the issue is the field on the userform.

When on the database the cell needs to show say £50.00

The field on the userform can show 50.00 if need be BUT when i open the userform & look in the Paid field it automatically just show what is in the cell from the database of which is £50.00
Up to this point is ok BUT if i just click save changes for the customer with or without an edit to its value this when when the cell on the database then shows £0.00

The answer somehow is when the userform imports the values from my database DO NOT let it import the £ sign BUT when i save the £ sign MUST be seen in the cell on the database.

Maybe your code sorts this but running into that error has now stopped me dead.