Sir,
I am new to VBA I will explain my problem, I have created a vba for a user form in which the data I entered should be saved in the data sheet2 viz Sheet("Data").Its working but if I press SUBMIT button incomplete data is also saving in the data sheet.In short I need the code as the data should be saved if and only if all the fields are filled in the user form.Please do make it correct.Here is my code
Private Sub commandbutton1_click()
Dim nextrow As Integer
'=COUNTA(A:A)+1
nextrow = WorksheetFunction.CountA(Sheets("Data").Range("A:A")) + 1
Sheets("Data").Cells(nextrow, 1) = Now
Sheets("Data").Cells(nextrow, 2) = UserForm1.TextBox1.Value
Sheets("Data").Cells(nextrow, 3) = UserForm1.ComboBox1.Value
Sheets("Data").Cells(nextrow, 4) = UserForm1.TextBox2.Value
Sheets("Data").Cells(nextrow, 5) = UserForm1.ComboBox2.Value
Sheets("Data").Cells(nextrow, 6) = UserForm1.ComboBox3.Value
Sheets("Data").Cells(nextrow, 7) = UserForm1.ComboBox4.Value
Sheets("Data").Cells(nextrow, 8) = UserForm1.ComboBox5.Value
If UserForm1.TextBox1.Value = Empty Then
MsgBox "Please enter Name.", vbExclamation
UserForm1.TextBox1.SetFocus
Exit Sub
End If
If UserForm1.ComboBox1.Value = Empty Then
MsgBox "Please choose Religion.", vbExclamation
UserForm1.ComboBox1.SetFocus
Exit Sub
End If
If UserForm1.TextBox2.Value = Empty Then
MsgBox "Please enter Caste.", vbExclamation
UserForm1.TextBox2.SetFocus
Exit Sub
End If
If UserForm1.ComboBox2.Value = Empty Then
MsgBox "Please choose Date of Birth.", vbExclamation
UserForm1.ComboBox2.SetFocus
Exit Sub
End If
If UserForm1.ComboBox3.Value = Empty Then
MsgBox "Please choose Month.", vbExclamation
UserForm1.ComboBox3.SetFocus
Exit Sub
End If
If UserForm1.ComboBox4.Value = Empty Then
MsgBox "Please choose Year.", vbExclamation
UserForm1.ComboBox4.SetFocus
Exit Sub
End If
If UserForm1.ComboBox5.Value = Empty Then
MsgBox "Please choose Gender.", vbExclamation
UserForm1.ComboBox5.SetFocus
Exit Sub
End If
Unload UserForm1
End Sub
Private Sub commandbutton2_click()
Unload UserForm1
End Sub
Private Sub userform_Activate()
Dim i As Integer
Dim c As Integer
Dim k As Integer
Dim Religion As String
Dim Gender As String
For i = 1 To 31
UserForm1.ComboBox2.AddItem i
Next i
For c = 1 To 12
UserForm1.ComboBox3.AddItem c
Next c
For k = 1970 To 2050
UserForm1.ComboBox4.AddItem k
Next k
UserForm1.ComboBox1.AddItem "Hindu"
UserForm1.ComboBox1.AddItem "Christian"
UserForm1.ComboBox1.AddItem "Muslim"
UserForm1.ComboBox5.AddItem "Male"
UserForm1.ComboBox5.AddItem "Female"
End Sub
Private Sub combobox2_keypress(ByVal keyascii As MSForms.ReturnInteger)
keyascii = 0
End Sub
Private Sub combobox3_keypress(ByVal keyascii As MSForms.ReturnInteger)
keyascii = 0
End Sub
Private Sub combobox4_keypress(ByVal keyascii As MSForms.ReturnInteger)
keyascii = 0
End Sub
Private Sub combobox5_keypress(ByVal keyascii As MSForms.ReturnInteger)
keyascii = 0
End Sub
Moderator Note:
Pls use code tags around your code next time as per forum rules.
Bookmarks