code to check blank textboxes in the userform with yes no msgbox

Dear Members,

I am using a worksheet, as a database table, and using a user form to fill the data into the worksheet. I am trying to save the data into the worksheet, by checking all the blank text boxes and presenting the user with choice of yes or no message box and let user decide the next action like the below scenario

I am using the following code, which checks the blank text boxes and shows ok message. It dont allow to save untill all the balnk text boxes will be filled. But i want the follwing scenario

[B]If I click the save button on the user form, if there is any blank textbox in the user form, a message box with “YES” “NO” buttons has to be pop up, with message like example “ Customer name was not entered Do you want to enter ”.

If I click “YES”, it has to go to the blank field. If I click “NO”, it has exit the message box and go to the next blank field if any. It has to check all blank fields, and then only save the data into the worksheet table.
I am sending the following code, which I am using.

I am not sure what is the exact vba code for the above scenario

Any help in this regard is greatly appreciated


I am using the following CODE Currently:

rivate Sub cmdsave_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("customerDetails")

'find first empty row in database
irow = ws.Cells(Rows.Count, 3) _
.End(xlUp).Offset(1, 0).Row

'check for customer name
If Trim(Me.txtcustname.Value) = "" Then
Me.txtcustname.SetFocus
MsgBox "Please enter the Customer Name", vbExclamation, "Customer Entry"
Exit Sub

End If


' code to check the blank textboxes


'check for invoice address 1
If Trim(Me.txtinvad1.Value) = "" Then
Me.txtinvad1.SetFocus
MsgBox "Please enter the Invoice Address 1", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for invoice address 2
If Trim(Me.txtinvad2.Value) = "" Then
Me.txtinvad2.SetFocus
MsgBox "Please enter the Invoice Address 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for invoice address 3
If Trim(Me.txtinvad3.Value) = "" Then
Me.txtinvad3.SetFocus
MsgBox "Please enter the Invoice Address 3", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Delivery Address 1
If Trim(Me.txtdelyad1.Value) = "" Then
Me.txtdelyad1.SetFocus
MsgBox "Please enter the Delivery Address 1", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Delivery Address 2
If Trim(Me.txtdelyad2.Value) = "" Then
Me.txtdelyad2.SetFocus
MsgBox "Please enter the Delivery Address 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Delivery Address 3
If Trim(Me.txtdelyad3.Value) = "" Then
Me.txtdelyad3.SetFocus
MsgBox "Please enter the Delivery Address 3", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for YOUR CST / ST TIN NO / CST TIN
If Trim(Me.txtcstno.Value) = "" Then
Me.txtcstno.SetFocus
MsgBox "Please enter the YOUR CST / ST TIN NO / CST TIN", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for YOUR TIN / VAT NO / LST TIN
If Trim(Me.txttinno.Value) = "" Then
Me.txttinno.SetFocus
MsgBox "Please enter the YOUR TIN / VAT NO / LST TIN", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for DL NO 1
If Trim(Me.txtdlno1.Value) = "" Then
Me.txtdlno1.SetFocus
MsgBox "Please enter YOUR DL NO 1", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for DL NO 2
If Trim(Me.txtdlno2.Value) = "" Then
Me.txtdlno1.SetFocus
MsgBox "Please enter YOUR DL NO 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for DL NO 2
If Trim(Me.txtdlno2.Value) = "" Then
Me.txtdlno1.SetFocus
MsgBox "Please enter YOUR DL NO 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Insurance Number
If Trim(Me.txtins.Value) = "" Then
Me.txtins.SetFocus
MsgBox "Please enter INSURANCE POLICY NUMBER", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for ECC NO
If Trim(Me.txteccno.Value) = "" Then
Me.txteccno.SetFocus
MsgBox "Please enter ECC NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for ST NO / LST NO
If Trim(Me.txtstno.Value) = "" Then
Me.txtstno.SetFocus
MsgBox "Please enter ST NO / LST NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for CST TIN NO
If Trim(Me.txtcsttinno.Value) = "" Then
Me.txtcsttinno.SetFocus
MsgBox "Please enter CST TIN NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for PAN NO
If Trim(Me.txtpanno.Value) = "" Then
Me.txtpanno.SetFocus
MsgBox "Please enter PAN NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'save the data to the database
ws.Cells(irow, 2).Value = Me.txtcustname.Value
ws.Cells(irow, 3).Value = Me.txtinvad1.Value
ws.Cells(irow, 4).Value = Me.txtinvad2.Value
ws.Cells(irow, 5).Value = Me.txtinvad3.Value
ws.Cells(irow, 6).Value = Me.txtdelyad1.Value
ws.Cells(irow, 7).Value = Me.txtdelyad2.Value
ws.Cells(irow, 8).Value = Me.txtdelyad3.Value
ws.Cells(irow, 9).Value = Me.txtcstno.Value
ws.Cells(irow, 10).Value = Me.txttinno.Value
ws.Cells(irow, 11).Value = Me.txteccno.Value
ws.Cells(irow, 12).Value = Me.txtdlno1.Value
ws.Cells(irow, 13).Value = Me.txtdlno2.Value
ws.Cells(irow, 14).Value = Me.txtstno.Value
ws.Cells(irow, 15).Value = Me.txtcsttinno.Value
ws.Cells(irow, 16).Value = Me.txtpanno.Value
ws.Cells(irow, 17).Value = Me.txtins.Value


'clear the data
Me.txtcustname.Value = ""
Me.txtinvad1.Value = ""
Me.txtinvad2.Value = ""
Me.txtinvad3.Value = ""
Me.txtdelyad1.Value = ""
Me.txtdelyad2.Value = ""
Me.txtdelyad3.Value = ""
Me.txtcstno.Value = ""
Me.txttinno.Value = ""
Me.txteccno.Value = ""
Me.txtdlno1.Value = ""
Me.txtdlno2.Value = ""
Me.txtstno.Value = ""
Me.txtcsttinno.Value = ""
Me.txtpanno.Value = ""
Me.txtins.Value = ""

End Sub
Regards
JWALA