• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to display a message when fields are blanks in a Userform and clear the fields

E

eddyrcabrera79

Member
Hi,

Attached is the sample Userform I'm working on it. It works perfect. all the information in being transfer to the excel document.

the Help I required is:

Once all the fields in the user form are filled, I press OK and I receive a display message "One record written to Sheet", I press OK and another display message comes up, "Do you want to enter another record?"

1 - After pressing "Yes" how can I make all the fields clear up in the Userform, in order to start inputting new information?

2 - In case one or more fields are empty because no data was input on them, How can I make a display message appear when I press the OK Bottom saying " Please check blank fields"

Please if you can make the corrections in the CODE and let me know by putting a comment what was added or what was included. I just want to see what the solution was to learn

Thanks in advance
 

Attachments

  • Test.xlsm
    27.1 KB · Views: 25
1 - Tack this on at end of your code for clicking OK button
Code: 
Unload Me
DinnerPlannerUserForm.Show

2 - Put this code before you transfer info to the sheet
Code: 
'Check for empty or False value for all checkbox and exit before submitting the form
With Me
If NameTextBox.Value = "" Then
        MsgBox "Name is required"
        Exit Sub
    ElseIf PhoneTextBox.Value = "" Then
        MsgBox "Phone# is required"
        Exit Sub
    ElseIf CityListBox.ListIndex < 0 Then
        MsgBox "Please select City"
        Exit Sub
    ElseIf DinnerComboBox.Value = "" Then
        MsgBox "Please select Dinner Preference"
        Exit Sub
    ElseIf DateCheckBox1.Value = False And DateCheckBox2.Value = False And DateCheckBox3.Value = False Then
        MsgBox "Please select Date"
        Exit Sub
    ElseIf MoneyTextBox.Value = "" Then
        MsgBox "Please enter maximum spend"
        Exit Sub
End If
End With
 

Attachments

  • Test.xlsm
    27.9 KB · Views: 48
Neat way to put everything into single message.

Just one thing I noticed. You are checking for Listobox.Value = Empty check.
For listbox you need Listbox.ListIndex <0 check instead.
 
Hi Chichiro and Vletm.

One more thing. there is a part of the code that is to display a message in case I want to enter or not a new record.

Code: 
response = MsgBox("Do you want to enter another record?", _
vbYesNo)

How can use the below format with the yes or not syntax. I would like to see the Exclamation symbol in the window.

Code: 
OutPut = MsgBox("Do you want to enter another record?", vbExclamation, "Example of vbExclamation")

thank you
 
Removed msgbox from 'Transfer information section
Modified code and added to Unload Me part.
Code: 
Dim YesNo As Integer
YesNo = MsgBox("Do you want to enter another record?", vbExclamation + vbYesNo)

If YesNo = vbYes Then
    Unload Me
    DinnerPlannerUserForm.Show
Else
    Unload Me
End If
 

Attachments

  • Test (1).xlsm
    28.8 KB · Views: 19
Hi Chichiro and Vletm,

Can you please let me know what is happening right now with my document?

If for some reason I forget to select or enter information on my Userform, I must receive a notification message advising me to enter the required info.

This notification is not working with the fields (City Preference & Date)

I haven't done anything to this part of the code.

Could you please help.

Thanks in advance
 

Attachments

  • Test2 .xlsm
    31.6 KB · Views: 5
Test this, changed following portion.
Code: 
If NameTextBox.Value = Empty Or _
        PhoneTextBox.Value = Empty Or _
        CityListBox.ListIndex < 0 Or _ 'changed from checking value
        DinnerComboBox.Value = Empty Or _
        (DateCheckBox1.Value = False And DateCheckBox2.Value = False And DateCheckBox3.Value = False) Or _ 'changed to check .value=False
 

Attachments

  • Test2 .xlsm
    31.6 KB · Views: 12
Hi Chihiro,

Now its working, its asking me to fill all the fields

there is only one problem with the City Preference notification message, if I dont select anything on the (City Preference) when I received the notification, "Please check blank Fields" Windows, I'm not able to see the name of the label on it.
 
Here's updated file. Forgot to change If statement on 2nd part.
 

Attachments

  • Test2 (1).xlsm
    31.8 KB · Views: 15
Thank you so much. Its working.

Would you mind helping me with something else?


I changed my welcome message to the below code: once I open the excel document it will say "Do you want to enter new data?", If I press Yes it will open the user form. How can I fix this code in order for when I press No the user form doesn't open.


Code: 
Private Sub Workbook_Open()


Dim Response As VbMsgBoxResult

YesNo = MsgBox("Do you want to enter new Data?", vbQuestion + vbYesNo, "Welcome")

If YesNo = vbYes Then


Else

   

End If


End sub


Sorry for the hard time, Not a VBA savvy.


Thank you

")
 
Here you go. You had Sub to auto show form. I've moved that portion of code to workbook open event and adjusted conditions.
 

Attachments

  • Test3.xlsm
    29.7 KB · Views: 22
How can I fix my code in order for the Userform to close by itself?.

Once you enter all the required information in the Userform you click in the OK bottom and you receive a message saying " Data Successfully Transfered", then you click OK and a new message pop up saying: "Do you want to enter a new record?" if you press YES, the Userform auto clear all the info and you can enter new Data.

the problem is when I receive the message: "Do you want to enter a new record?" and I press no, I would like the Userform to close.
 
try changing "unload me" to "unload dinnerplanneruserform"

you might also need to add a subroutine in the main macro to check
 
I tried putting "unload dinnerplanneruserform" and even if I press Yes or No, the document close.

the code in the attached document when I press Yes, clear the info and I'm able to enter new data. The problem is when I press No, that the Userform doesn't close.
 

Attachments

  • Test3 (3).xlsm
    29.8 KB · Views: 23
Figured it out. In the code for the ok button. this bit needs to be changed.

Code: 
YesNo = MsgBox("Do you want to enter another record?", vbExclamation + vbYesNo)

If YesNo = vbYes Then

Else
    Unload Me
End If


Unload Me
DinnerPlannerUserForm.Show

End If

to this
Code: 
YesNo = MsgBox("Do you want to enter another record?", vbExclamation + vbYesNo)

If YesNo = vbYes Then
    DinnerPlannerUserForm.Show
Else
    Unload Me
End If

End If


Basically your code was first telling it to exit userform then start it again
 
Hi Chirayu,

Now the Userform close by itself when I press No. But now there is something else happening.

Once I enter all the data and I press OK, I receive the message "Data Successfully Transferred", then I press OK. I receive the message "Do you want to enter a new record?" , if I press Yes now I receive the error message (Run-time error 400, form already displayed; cant show modally)

I need when I press Yes to clear all the fields in order for me to enter new data
 
Code: 
YesNo = MsgBox("Do you want to enter another record?", vbExclamation + vbYesNo)

If YesNo = vbYes Then
    Unload Me
    DinnerPlannerUserForm.Show
Else
    Unload Me
End If

End If
 
You must log in or register to reply here.
Back
Top