CODEWITHSUNDEEP
excelvba
jwala
jwala

Reputation: 1

Check blank textboxes in the userform to warn with Message box

I am using a worksheet, as a database table. I have a userform to fill data.

I want to code the following scenario.

When I click the save button on the userform, if there is any blank textbox in the userform, a message box with “YES” “NO” buttons has to be pop up, with a message like “ 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 to 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 have the following code which saves the date from the userform to the first available blank row in the worksheet table, but doesn’t check any blank text boxes in the userform.

Private 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


    '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

Upvotes: 0

Views: 13292

Answers (1)

Steve Homer
Steve Homer

Reputation: 3922

If you store the narrative name of your textbox ("Customer Address" for example) in the tag property of the control then you could use something like the below.


Private Sub CommandButton1_Click()
Dim t As Control, res As VbMsgBoxResult

For Each t In Me.Controls
    If TypeName(t) = "TextBox" Then ' Make sure we're only looking at textboxes.
        If t.Text = vbNullString And t.Tag <> vbNullString Then
            res = MsgBox("You've not completed the " + t.Tag + " field. Would you like to complete it now?", vbYesNo + vbQuestion)

            If res = vbYes Then Exit Sub
    End If
Next

End Sub

Upvotes: 2

Related Questions