Dear Members,
I am using a worksheet, as a database table, and using a user form to fill the data into the worksheet. I want help in the following scenario.
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.
This code saves the date from the user form to the first available blank row in the worksheet table, but doesn’t check any blank text boxes in the user form.
Any help in this regard is greatly appreciated
I am using the following code:
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
Thanks
I am using a worksheet, as a database table, and using a user form to fill the data into the worksheet. I want help in the following scenario.
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.
This code saves the date from the user form to the first available blank row in the worksheet table, but doesn’t check any blank text boxes in the user form.
Any help in this regard is greatly appreciated
I am using the following code:
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
Thanks