What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub

You need to clarify your requirement. What specific field is it that you
want to queue up the message box? I see a UserForm 4 being displayed with no
apparent related code. Is this what you are referring to as a message box?
An explanation related to your code would help.

"Ewing25" wrote:

What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub

Ah sorry In the coe i have textbox3 and textbox4 display userform4 if they
are blank. Userform4 is just a userform i made to tell them to fill in the
fields. And yes that was my message box i described in the original text.

I just need to know how to make it so when they click out of userform4 it
keeps the values the user already put into the form and just cancels the
macro process.

"JLGWhiz" wrote:

You need to clarify your requirement. What specific field is it that you
want to queue up the message box? I see a UserForm 4 being displayed with no
apparent related code. Is this what you are referring to as a message box?
An explanation related to your code would help.

"Ewing25" wrote:

What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub

It is a little difficult to do this efficiently, without knowing what
UserForm4 is doing, but I will try to give you an idea of how to approach it.

RETRY:
If TextBox3.Text = "" Then
UserForm4.Show
If TextBox3.Text = "" Then
GoTo RETRY:
End If
End If

The above code sets up a conditional loop using the label RETRY: If after
UserForm4 has executed and closed the TextBox is still empty, then it will
loop until something is entered into the TextBox.


"Ewing25" wrote:

Ah sorry In the coe i have textbox3 and textbox4 display userform4 if they
are blank. Userform4 is just a userform i made to tell them to fill in the
fields. And yes that was my message box i described in the original text.

I just need to know how to make it so when they click out of userform4 it
keeps the values the user already put into the form and just cancels the
macro process.

"JLGWhiz" wrote:

You need to clarify your requirement. What specific field is it that you
want to queue up the message box? I see a UserForm 4 being displayed with no
apparent related code. Is this what you are referring to as a message box?
An explanation related to your code would help.

"Ewing25" wrote:

What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub

All userform4 does is show some text saying they need to fill in the field
and theres a button that says okay which they push to go back to the form.

I tried your code and it makes an infinite loop.

The whole macro is pretty simple.

The user clicks on button5 which is located on a worksheet named "Trips".
This then brings up Userform3 which is where the textboxs are located.
Textbox3 and Textbox4 are required from the user. If the user doesnt input
anything in these fields when they click on Commandbutton1 i am trying to get
a message that pops up telling them to input something. then when they press
okay on this message it goes away allowing the user to make changes to the
userform.

Im sorry im not doing a real good job of explaining this.

I really appreciate your help.

"JLGWhiz" wrote:

It is a little difficult to do this efficiently, without knowing what
UserForm4 is doing, but I will try to give you an idea of how to approach it.

RETRY:
If TextBox3.Text = "" Then
UserForm4.Show
If TextBox3.Text = "" Then
GoTo RETRY:
End If
End If

The above code sets up a conditional loop using the label RETRY: If after
UserForm4 has executed and closed the TextBox is still empty, then it will
loop until something is entered into the TextBox.


"Ewing25" wrote:

Ah sorry In the coe i have textbox3 and textbox4 display userform4 if they
are blank. Userform4 is just a userform i made to tell them to fill in the
fields. And yes that was my message box i described in the original text.

I just need to know how to make it so when they click out of userform4 it
keeps the values the user already put into the form and just cancels the
macro process.

"JLGWhiz" wrote:

You need to clarify your requirement. What specific field is it that you
want to queue up the message box? I see a UserForm 4 being displayed with no
apparent related code. Is this what you are referring to as a message box?
An explanation related to your code would help.

"Ewing25" wrote:

What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub

Ok i adapted ur code a little to look like this im just not sure what to put
to keep the macro from executing. I put stop but i dont think its valid.

If TextBox3.Text = "" Then
UserForm4.Show
If TextBox3.Text = "" Then
Unload UserForm4
If TextBox3.Text or TextBox4.Text = "" Then
Stop

End If
End If
End If

"JLGWhiz" wrote:

It is a little difficult to do this efficiently, without knowing what
UserForm4 is doing, but I will try to give you an idea of how to approach it.

RETRY:
If TextBox3.Text = "" Then
UserForm4.Show
If TextBox3.Text = "" Then
GoTo RETRY:
End If
End If

The above code sets up a conditional loop using the label RETRY: If after
UserForm4 has executed and closed the TextBox is still empty, then it will
loop until something is entered into the TextBox.


"Ewing25" wrote:

Ah sorry In the coe i have textbox3 and textbox4 display userform4 if they
are blank. Userform4 is just a userform i made to tell them to fill in the
fields. And yes that was my message box i described in the original text.

I just need to know how to make it so when they click out of userform4 it
keeps the values the user already put into the form and just cancels the
macro process.

"JLGWhiz" wrote:

You need to clarify your requirement. What specific field is it that you
want to queue up the message box? I see a UserForm 4 being displayed with no
apparent related code. Is this what you are referring to as a message box?
An explanation related to your code would help.

"Ewing25" wrote:

What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub

since it's in a button_click event, just put

If TextBox3.Text or TextBox4.Text = "" Then
exit sub

your userform will still be there, and they can always click the
button again. you might want to add a message box before "exit sub"
that says

msgbox "there must be a value entered into........" , vbokonly

(whatever you've called that textbox.)

hope that helps
susan



On Jun 11, 3:09*pm, Ewing25 wrote:
Ok i adapted ur code a little to look like this im just not sure what to put
to keep the macro from executing. I put stop but i dont think its valid.

If TextBox3.Text = "" Then
* * *UserForm4.Show
* * *If TextBox3.Text = "" Then
* * * * *Unload UserForm4
If TextBox3.Text or TextBox4.Text = "" Then
* * * * * *Stop

* * * * End If
* * *End If
End If



"JLGWhiz" wrote:
It is a little difficult to do this efficiently, without knowing what
UserForm4 is doing, but I will try to give you an idea of how to approach it.

RETRY:
If TextBox3.Text = "" Then
* * *UserForm4.Show
* * *If TextBox3.Text = "" Then
* * * * *GoTo RETRY:
* * *End If
End If

The above code sets up a conditional loop using the label RETRY: *If after
UserForm4 has executed and closed the TextBox is still empty, then it will
loop until something is entered into the TextBox.

"Ewing25" wrote:

Ah sorry In the coe i have textbox3 and textbox4 display userform4 if they
are blank. Userform4 is just a userform i made to tell them to fill in the
fields. And yes that was my message box i described in the original text.

Yes i just figured that out.

Hah

Thank you both for your help!

"Susan" wrote:

since it's in a button_click event, just put

If TextBox3.Text or TextBox4.Text = "" Then
exit sub

your userform will still be there, and they can always click the
button again. you might want to add a message box before "exit sub"
that says

msgbox "there must be a value entered into........" , vbokonly

(whatever you've called that textbox.)

hope that helps
susan



On Jun 11, 3:09 pm, Ewing25 wrote:
Ok i adapted ur code a little to look like this im just not sure what to put
to keep the macro from executing. I put stop but i dont think its valid.

If TextBox3.Text = "" Then
UserForm4.Show
If TextBox3.Text = "" Then
Unload UserForm4
If TextBox3.Text or TextBox4.Text = "" Then
Stop

End If
End If
End If



"JLGWhiz" wrote:
It is a little difficult to do this efficiently, without knowing what
UserForm4 is doing, but I will try to give you an idea of how to approach it.

RETRY:
If TextBox3.Text = "" Then
UserForm4.Show
If TextBox3.Text = "" Then
GoTo RETRY:
End If
End If

The above code sets up a conditional loop using the label RETRY: If after
UserForm4 has executed and closed the TextBox is still empty, then it will
loop until something is entered into the TextBox.

"Ewing25" wrote:

Ah sorry In the coe i have textbox3 and textbox4 display userform4 if they
are blank. Userform4 is just a userform i made to tell them to fill in the
fields. And yes that was my message box i described in the original text.

I just need to know how to make it so when they click out of userform4 it
keeps the values the user already put into the form and just cancels the
macro process.

"JLGWhiz" wrote:

You need to clarify your requirement. What specific field is it that you
want to queue up the message box? I see a UserForm 4 being displayed with no
apparent related code. Is this what you are referring to as a message box?
An explanation related to your code would help.

"Ewing25" wrote:

What im trying to do is make it so when someone doesnt input a value in a
certain field it comes up with a msgbox that tells them to fill it in. Then
when they press Okay on the msgbox it goes back to the form without
submitting it.

So far i have everything except making it so when you click okay on the
message box it goes back to the form without submitting the data or clearing
the form. Also just to make it so the warning message only appears once even
if they dont fill in 2 required fields.

Heres my code:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Trip")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrA = wks.Cells(Rows.Count, 5).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 6).End(xlUp).Row

wks.Range("B" & lrA + 1) = ListBox1.Text
wks.Range("F" & lrA + 1) = TextBox1.Text
wks.Range("D" & lrA + 1) = TextBox3.Text
If TextBox3.Text = "" Then
UserForm4.Show
End If

wks.Range("E" & lrA + 1) = TextBox4.Text
If TextBox4.Text = "" Then
UserForm4.Show
End If

wks.Range("A" & lrA + 1) = TextBox5.Text
wks.Range("C" & lrA + 1) = TextBox6.Text
TextBox1.Text = ""
TextBox4.Text = ""
TextBox3.Text = ""
TextBox6.Text = ""

End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
End Sub- Hide quoted text -

- Show quoted text -