This is more linear. It colors light red the textboxes that need input, and
gives only one message to disturb the user. Put this into a regular code
module, and run it from the BeforeSave event procedure or whenever else it
is required.


Sub ValidateTheSheet
Dim sMessage as String

With Worksheets("human health csm")

If .SurfaceCheck6.Value And Len(.SurfaceSoilTextbox.Text) = 0 Then
' need input: add to message, color textbox light red
sMessage = sMessage & "Please enter a transport mechanism for
the Surface Soil section." & vbNewLine
.SurfaceSoilTextbox.BackColor = &H00C0C0FF&
Else
' input provided or not needed, color textbox white
.SurfaceSoilTextbox.BackColor = &H80000005&
End If

If .SurfaceCheck3.Value And Len(.SubSurfaceSoilTextbox.Text) = 0
Then
' need input: add to message, color textbox light red
sMessage = sMessage & "Please enter a transport mechanism for
the Subsurface Soil section." & vbNewLine
.SubSurfaceSoilTextbox.BackColor = &H00C0C0FF&
Else
' input provided or not needed, color textbox white
.SubSurfaceSoilTextbox.BackColor = &H80000005&
End If

' three more checks (not shown here)

End With

MsgBox sMessage, vbOkOnly + vbExclamation, "Finish filling out the form"

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Hannah Lu" <Hannah wrote in message
...
Hi,
I have a worksheet with five checkboxes and text boxes. If a checkbox is
checked, data should be entered in the corresponding text box. I'd like an
input box (or some other form of validation - open to suggestions) to pop
up
if the checkbox is checked and the text box is empty. I figured the
easiest
way is to put the code in the Thisworkbook module, beforesave, and I can
get
it to work using multiple nested If statements, but there has to be an
easier
way! Otherwise I'll end up with a mile of code that's hard to navigate. Is
there some loop I could use? A sample of the code I'm working with now is
below, thank you so much for your help!

Check boxes and corresponding text boxes a
-SurfaceCheck6 and SurfaceSoilTextbox
-SubSurfaceCheck3 and SubSurfaceSoilTextbox
-GroundwaterCheck5 and GroundwaterTextbox
-SurfaceWaterCheck4 and SurfaceWaterTextbox
-SedimentCheck3 and SedimentTextbox

If Worksheets("human health csm").Visible = True Then
Worksheets("human health csm").Select
With ActiveSheet
'Checks if the "Other" checkbox is checked/unchecked for Surface Soils
If .SurfaceCheck6.Value = True And .SurfaceSoilTextbox.Text = "" Then
'value if true
.SurfaceSoilTextbox.Activate
.SurfaceSoilTextbox.Value = Application.InputBox("Please enter a
transport mechanism for the Surface Soil section.", "Please enter a
transport
mechanism.")
If .SurfaceSoilTextbox.Value = "FALSE" Then
.SurfaceSoilTextbox.Value = ""
End If
'checks if the "Other" checkbox is checked/unchecked for Subsurface
soils and cancels if necessary.
If .SubSurfaceCheck3.Value = True And .SubsurfaceSoilTextbox.Text = ""
Then
.SubsurfaceSoilTextbox.Activate
.SubsurfaceSoilTextbox.Value = Application.InputBox("Please enter a
transport mechanism for the Subsurface Soil section.", "Please enter a
transport mechanism.")
If .SubsurfaceSoilTextbox.Value = "FALSE" Then
.SubsurfaceSoilTextbox.Value = ""
End If
End If
End If
End Sub