Message box to appear if option not selected

J

joacro

Board Regular
Joined
Jun 24, 2010
Messages
158
Hi there

I wish that a message box to appear if no option is selected:
- if txtCh1 = true and opt 1, opt2 or opt3 is not selected then msgbox (please select age group of child / adult dependant)
- if txtCh2 = true and opt4, opt5 or opt6 is not selected then msgbox (please select age group of child / adult dependant)
- if txtCh3 = true and opt7, opt8 or opt9 is not selected then msgbox (please select age group of child / adult dependant)
- if txtCh4 = true and opt10, opt11 or opt12 is not selected then msgbox (please select age group of child / adult dependant)
- if txtCh5 = true and opt13, opt14 or opt 15is not selected then msgbox (please select age group of child / adult dependant)
- if txtCh6 = true and opt16, opt17 or opt18 is not selected then msgbox (please select age group of child / adult dependant)

Hope someone can help me with this

Thanx in advance

Joacro
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hello,

I am unclear as to your question. I somewhat understand, however the description has lost me.

What exactly is txtCH1, txtCH2, ... and opt1, opt2, ...?
(Variables? Option buttons?)
 
Upvote 0
Hi there,

txtCh1 - txtCh6 = textboxes
and opt1 = opt18 = option buttons
hope this helps

Basically if txtCh1.value = true and opt 1, opt2 or opt3 is not seclected I would like a messagebox to appear
 
Upvote 0
Are these controls placed in worksheet or in a userform? Is user typing true or false in those textboxes or does it contain a drop down ?
 
Last edited:
Upvote 0
This is untested on my end, see if this works (on a copy) while I try to duplicate the textboxes and option buttons...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>j = 0<br><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 6<br>    <SPAN style="color:#00007F">If</SPAN> Me.txtCh & i.Value = "True" And opt & j + 1 = <SPAN style="color:#00007F">False</SPAN> And _<br>            opt & j + 2 = <SPAN style="color:#00007F">False</SPAN> And opt & j + 3 = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "please select age group of child / adult dependant"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    j = j + 3<br><SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
I should add this was attached to a command button.

I have already duplicated the textboxes and option buttons. I do not think I will need the actual sheet.
 
Upvote 0
My command button is Add: I already have this code there,

Where can I insert your code to make this posible?

Private Sub CmdAdd_Click()
LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LR) = txtPrin
Range("I" & LR) = txtTotal
Range("A" & LR + 1) = txtSpouse

If txtSpouse.Value = "" Then myoffset = 0 Else myoffset = 1
If opt1.Value = True Then
Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (kid u 2)"
ElseIf opt2.Value = True Then
Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (kid u 18)"
ElseIf opt3.Value = True Then
Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (over 18)"
End If
If opt4.Value = True Then
Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (kid u 2)"
ElseIf opt5.Value = True Then
Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (kid u 18)"
ElseIf opt6.Value = True Then
Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (over 18)"
End If
If opt7.Value = True Then
Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (kid u 2)"
ElseIf opt8.Value = True Then
Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (kid u 18)"
ElseIf opt9.Value = True Then
Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (over 18)"
End If
If opt10.Value = True Then
Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (kid u 2)"
ElseIf opt11.Value = True Then
Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (kid u 18)"
ElseIf opt12.Value = True Then
Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (over 18)"
End If
If opt13.Value = True Then
Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (kid u 2)"
ElseIf opt14.Value = True Then
Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (kid u 18)"
ElseIf opt15.Value = True Then
Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (over 18)"
End If
If opt16.Value = True Then
Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (kid u 2)"
ElseIf opt17.Value = True Then
Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (kid u 18)"
ElseIf opt18.Value = True Then
Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (over 18)"
End If
End Sub
 
Upvote 0
Maybe:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdAdd_Click()<br>LR = Cells(Rows.Count, "A").End(xlUp).Row + 1<br>Range("A" & LR) = txtPrin<br>Range("I" & LR) = txtTotal<br>Range("A" & LR + 1) = txtSpouse<br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>j = 0<br><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 6<br>        <SPAN style="color:#00007F">If</SPAN> UCase(Me.Controls("txtCh" & i).Value) = "TRUE" And Me.Controls("opt" & j + 1).Value = <SPAN style="color:#00007F">False</SPAN> And _<br>                Me.Controls("opt" & j + 2).Value = <SPAN style="color:#00007F">False</SPAN> And Me.Controls("opt" & j + 3).Value = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "please select age group of child / adult dependant"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        j = j + 3<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">If</SPAN> txtSpouse.Value = "" <SPAN style="color:#00007F">Then</SPAN> myoffset = 0 <SPAN style="color:#00007F">Else</SPAN> myoffset = 1<br><SPAN style="color:#00007F">If</SPAN> opt1.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (kid u 2)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt2.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (kid u 18)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt3.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (over 18)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> opt4.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (kid u 2)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt5.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (kid u 18)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt6.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (over 18)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> opt7.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (kid u 2)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt8.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (kid u 18)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt9.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (over 18)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> opt10.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (kid u 2)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt11.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (kid u 18)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt12.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (over 18)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> opt13.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (kid u 2)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt14.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (kid u 18)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt15.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (over 18)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> opt16.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (kid u 2)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt17.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (kid u 18)"<br><SPAN style="color:#00007F">ElseIf</SPAN> opt18.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (over 18)"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Error: Multiple entry on sheet from userform
Replies
4
Views
703
joacro
J

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top