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

 

[nemmi69]

Not sure if you wanted it to loop back. tr this.

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
Exit Sub


myerror:
MsgBox "Please fill every field!", vbCritical, ThisWorkbook.Name
On Error GoTo 0

 

[Norie]

Perhaps.

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 = CDbl(.Text)

            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With

 

[dmt32]

Hi,

In the the rest of your code you have not shared should be this line

'New record - check all fields entered
    If Not IsComplete(Form:=Me) Then Exit Sub

This calls a function to validate all fields have been completed before writing data to the workheet - Do you have the function in your project?

Dave

 

[ipbr21054]

I have now supplied the full code as mentioned by dmt32.

nemmie69 your code advised me that there was an myerror code in place already.

Norie you ocde would advise me that txtpaid was not filled with a value,i entered a value but the save would continue without advising me that the other fields were empty.

dmt32 i have now shown the complete code as it is in place on my sheet but does not advise me the fields are empty thus showing the run time error.

Private Sub UpdateRecord_Click()

Dim C As Range
Dim i As Integer
Dim Msg As String
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
    If Not IsComplete(Form:=Me) Then Exit Sub
        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
                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


    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

 

[Norie]

With my code if txtpaid was empty the code would exit.

Perhaps you should be validating input before saving anything.

 

[ipbr21054]

When i clicked on save i then seen a message to advise txtpaid was empty.
I then entered a value & clicked on save of which it did & was expecting it to advise me that others were also empty.

 

[dmt32]

dmt32 i have now shown the complete code as it is in place on my sheet but does not advise me the fields are empty thus showing the run time error.

This line in your code is to validate data entry

If Not IsComplete(Form:=Me) Then Exit Sub

The function IsComplete cycles through ALL your Textbox controls to check a values have been entered BEFORE your code writes data to the worksheet. If the function is not vlaidating one of the textboxes then check that the control is listed in the ControlNames function.

Do note that the function does not check if the correct data type has been entered (numbers, dates etc) in each of the textboxes & where your code coerces string values from textboxes you will get an error if invalid data entered.

You can try change this line.

ws.Cells(r, i).Value = CDbl(.Text)

to this

ws.Cells(r, i).Value = Val(.Text)

The Val function is a bit more forgiving for invalid data entry.

Dave

 

[Norie]

When i clicked on save i then seen a message to advise txtpaid was empty.
I then entered a value & clicked on save of which it did & was expecting it to advise me that others were also empty.

Are you sure it was the code I posted that was showing that message?

 

[ipbr21054]

This line in your code is to validate data entry

If Not IsComplete(Form:=Me) Then Exit Sub

The function IsComplete cycles through ALL your Textbox controls to check a values have been entered BEFORE your code writes data to the worksheet. If the function is not vlaidating one of the textboxes then check that the control is listed in the ControlNames function.

Do note that the function does not check if the correct data type has been entered (numbers, dates etc) in each of the textboxes & where your code coerces string values from textboxes you will get an error if invalid data entered.

You can try change this line.

ws.Cells(r, i).Value = CDbl(.Text)

to this

ws.Cells(r, i).Value = Val(.Text)

The Val function is a bit more forgiving for invalid data entry.

Dave

Thanks,
I have now changed that and left 1 field empty.
Clicking save still ignores the empty field and continues to save.