Run time error 13

[ipbr21054]

Evening,
Last post before bed.

Below is a section of code from my userform,i think the section i have supplied will be ok for what is required as opposed to showing it all.
My userform has 15 textboxes.
All 15 must be filled & not left empty when i click on save,currently if any are left empty and i click save i then see a run time error 13
When i look at the code this section is shown in yellow ws.Cells(r, i).Value = CDbl(.Text)

So as opposed to showing me the run time error can we have a msgbox appear advising me of the empty fields etc.
Obviously if all fields are complete the save goes ahead.

    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
                ws.Cells(r, i).Value = CDbl(.Text)
            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With

 

[ipbr21054]

So i need to make this list the same order as my fields on the userform ?

Is that correct or will it screw anything up.

Thanks

 

[dmt32]

So i need to make this list the same order as my fields on the userform ?

Is that correct or will it screw anything up.

Thanks

If you change the order of the control names in the Function array the data from your textboxes will output to your worksheet in the changed order.


Dave

 

[ipbr21054]

You are correct.
Having the fields come down in order does work but like you say the date in then in a field which has the label say paid etc.

Think i would rather put up with out of order fields.

Thanks.

 

[ipbr21054]

Hi,
Just an update.
Ive been looking at this 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.

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

 

[dmt32]

Hi,
this is a different question to original post but having quick look at the code

- 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

 

[ipbr21054]

Hi,
Sorry,ive started a new post here if you could reply the same again please.

thanks

https://www.mrexcel.com/forum/excel...save-1-field-entry-correctly.html#post5117480