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]

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

Hi,
This is what happens with your code.
If the field txtpaid has a value but any other field is empty the save goes ahead & doesnt advise me that a field is empty.

BUT

If the field txtpaid is empty & i click on save i see a message txtPaid is empty OK.
I click on ok and enter a value in the txtpaid field.
I then click on save & the save goes ahead but doesnt tell me another field is empty.

 

[Norie]

This is the code I posted.

                If .Text = "" Then
                     MsgBox .Name & " is empty!"
                     Exit Sub
                End If

If txtPaid is empty it will display a message 'txtPaid is empty!' with this line of code,

MsgBox .Name & " is empty!"

which is followed by this line of code,

Exit Sub

which exits the sub.

 

[dmt32]

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

Does that control name exist in the ControlNames function?

Dave

 

[ipbr21054]

This is the code I posted.

                If .Text = "" Then
                     MsgBox .Name & " is empty!"
                     Exit Sub
                End If

If txtPaid is empty it will display a message 'txtPaid is empty!' with this line of code,

MsgBox .Name & " is empty!"

which is followed by this line of code,

Exit Sub

which exits the sub.

That is the code i used & it does what i mentioned.
It advises of the one field only.
Otherwise if filled it then saves.

 

[ipbr21054]

Does that control name exist in the ControlNames function?

Dave

My learning disabilities cant find out how to answer that question.

I deleted some other fields so they were also empty but still the save continues & no msg etc about empty fields,does that answer it ?

Sorry.

 

[dmt32]

My learning disabilities cant find out how to answer that question.

I deleted some other fields so they were also empty but still the save continues & no msg etc about empty fields,does that answer it ?

Sorry.

No worries - In your VBA project you have a Function called ControlNames - this is returning an array of each of your control names. Is the Textbox named "txtPaid"

listed in the functions array? If not, you need to add it. The Function IsComplete calls the ControlNames array to ensure each control has data entered.


Dave

 

[ipbr21054]

This is the names as requested.

Function ControlNames() As VariantControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
                    "txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
                    "txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
                    "txtJobDate", "txtVehicleYear", "txtPaid", "txtInvoiceNumber", "TextBox1")
End Function

Also this.

Function IsComplete(ByVal Form As Object) As Boolean    Dim i As Integer
    For i = 1 To UBound(ControlNames)
        IsComplete = CBool(Len(Form.Controls(ControlNames(i)).Text) > 0)
        If Not IsComplete Then
            MsgBox "PLEASE COMPLETE ALL FIELDS", 17, "Entry Required"
            Form.Controls(ControlNames(i)).BackColor = vbYellow
            Form.Controls(ControlNames(i)).SetFocus
            Exit Function
        End If
    Next i
End Function

 

[dmt32]

ok think I see where your issue is

Look for the line shown in RED in the Sub UpdateRecord_Click code & MOVE it to the place I have shown i.e the first line of code after the variable declarations.

Private Sub UpdateRecord_Click()
    Dim i As Integer
    Dim IsNewCustomer As Boolean
    Dim Msg As String
    
    If Not IsComplete(Form:=Me) Then Exit Sub


'
'REST OF YOUR CODE
'
'

This should now capture blank data entry.

Dave

 

[ipbr21054]

dmt32,
Thanks for the time put into this,it now will not let me save untill all fields on userform are complete.

The field is shown in yellow to advise me its empty,clicking save them prompts me again if another field in empty.

Thanks very much it now works great.

Can i ask a question about the order in which it selects to advise me that the field is empty.
Say my fields are like below,i assume the tab index ref is how it works ?

1 2 3
4 5 6
7 8 9

etc etc

As opposed to starting from tab index field 1 then checking 2,3,4 etc etc i see it like 1,7,2 all out of order.
Is this normal & should i just leave it alone.

Basically where does it get its order from to advise me which is empty ?

Thanks

 

[dmt32]

Basically where does it get its order from to advise me which is empty ?

Thanks

From the function ControlNames

Function ControlNames() As Variant
ControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
                    "txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
                    "txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
                    "txtJobDate", "txtVehicleYear", "txtPaid", "txtInvoiceNumber", "TextBox1")
End Function

This is the order data entry is tested & placed to the worksheet.

Dave