| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simpler way for nested If statements (VBA)
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simpler way for nested If statements (VBA)
Hi,
the code bellow runs as follow: run Test It calls ValidateEntries which validates all entries and return True/False ValidateEntries calls ValidateEntry on each control pair and return True/False (True=Valid, False=Not Valid) What you need to do: In the section 'User Initialization of variables' of function ValidateEntries, replace the values of the names of the checkbox and textbox arrays. It should NOT be the variable names (eg:SurfaceCheck6), but their Name property i.e. the one that shows in Excel 's name box (top-left) when you select the control. The arrays items should be ordered properly so that the i checkbox correspond to the i textbox in the arrays. ''' #################################### Sub test() Dim result As Boolean result = ValidateEntries() Debug.Print result End Sub Function ValidateEntries() As Boolean Dim arrChks ''' array of names of checkboxes Dim arrTxts ''' array of names of textboxes Dim chk As MSForms.CheckBox ''' a checkbox Dim txt As MSForms.TextBox ''' a textbox Dim wsh As Worksheet Dim ttl As Long, i As Long ''' --- User Initialization of variables --------- arrChks = Array("SurfaceCheck6", "SubSurfaceCheck3", "GroundwaterCheck5", "SurfaceWaterCheck4", "SedimentCheck3") arrTxts = Array("SurfaceSoilTextbox", "SubSurfaceSoilTextbox", "GroundwaterTextbox", "SurfaceWaterTextbox", "SedimentTextbox") Set wsh = Worksheets("human health csm") ''' ------------------------------------- ''' ---- General Initialization -------------- ttl = UBound(arrChks) - LBound(arrChks) + 1 i = LBound(arrChks) ValidateEntries = True ''' -------- Validate in loop -------------- ''' loop and stop when finish or when 1 is not valid Do While ValidateEntries And i <= UBound(arrChks) Set chk = wsh.OLEObjects(arrChks(i)).Object ''' initialize CHeckbox Set txt = wsh.OLEObjects(arrTxts(i)).Object ''' initialize corresponding textbox ValidateEntries = ValidateEntry(chk, txt) Loop End Function Function ValidateEntry(chk As MSForms.CheckBox, txt As MSForms.TextBox) As Boolean 'Checks if the "Other" checkbox is checked/unchecked for Surface Soils If chk.Value = True And txt.Text = "" Then 'value if true txt.Value = Application.InputBox("Please enter a transport mechanism for the Surface Soil section.", "Please enter a transportmechanism.") If txt.Value = "FALSE" Then txt.Value = "" End If ValidateEntry = (txt.Value < "") Else ValidateEntry = True End If End Function ''' ####################################### -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Hannah Lu" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simpler way for nested If statements (VBA)
Note about:
------------- "It should NOT be the variable names (eg:SurfaceCheck6), but their Name property i.e. the one that shows in Excel 's name box (top-left) when you select the control" ------------------------- Actually, for OleObjects, the variable name is the name shown in the name box. So, no need to change anything here. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "sebastienm" wrote: Hi, the code bellow runs as follow: run Test It calls ValidateEntries which validates all entries and return True/False ValidateEntries calls ValidateEntry on each control pair and return True/False (True=Valid, False=Not Valid) What you need to do: In the section 'User Initialization of variables' of function ValidateEntries, replace the values of the names of the checkbox and textbox arrays. It should NOT be the variable names (eg:SurfaceCheck6), but their Name property i.e. the one that shows in Excel 's name box (top-left) when you select the control. The arrays items should be ordered properly so that the i checkbox correspond to the i textbox in the arrays. ''' #################################### Sub test() Dim result As Boolean result = ValidateEntries() Debug.Print result End Sub Function ValidateEntries() As Boolean Dim arrChks ''' array of names of checkboxes Dim arrTxts ''' array of names of textboxes Dim chk As MSForms.CheckBox ''' a checkbox Dim txt As MSForms.TextBox ''' a textbox Dim wsh As Worksheet Dim ttl As Long, i As Long ''' --- User Initialization of variables --------- arrChks = Array("SurfaceCheck6", "SubSurfaceCheck3", "GroundwaterCheck5", "SurfaceWaterCheck4", "SedimentCheck3") arrTxts = Array("SurfaceSoilTextbox", "SubSurfaceSoilTextbox", "GroundwaterTextbox", "SurfaceWaterTextbox", "SedimentTextbox") Set wsh = Worksheets("human health csm") ''' ------------------------------------- ''' ---- General Initialization -------------- ttl = UBound(arrChks) - LBound(arrChks) + 1 i = LBound(arrChks) ValidateEntries = True ''' -------- Validate in loop -------------- ''' loop and stop when finish or when 1 is not valid Do While ValidateEntries And i <= UBound(arrChks) Set chk = wsh.OLEObjects(arrChks(i)).Object ''' initialize CHeckbox Set txt = wsh.OLEObjects(arrTxts(i)).Object ''' initialize corresponding textbox ValidateEntries = ValidateEntry(chk, txt) Loop End Function Function ValidateEntry(chk As MSForms.CheckBox, txt As MSForms.TextBox) As Boolean 'Checks if the "Other" checkbox is checked/unchecked for Surface Soils If chk.Value = True And txt.Text = "" Then 'value if true txt.Value = Application.InputBox("Please enter a transport mechanism for the Surface Soil section.", "Please enter a transportmechanism.") If txt.Value = "FALSE" Then txt.Value = "" End If ValidateEntry = (txt.Value < "") Else ValidateEntry = True End If End Function ''' ####################################### -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Hannah Lu" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simpler way for nested If statements (VBA)
Frankly, I'd bend over backwards to find a way to turn this into a simple
loop if at all possible. Here is one approach: Create a table 4 columns wide Col A: CheckboxName Col B: TextboxName Col C: InputBoxPrompt Col D; InputBox Caption Fill in the corresponding data (5 rows per your example). Give the range a Name ("ValidationData"). Adjust your code to loop through the rows of the range. The sheet containing all this data can be hidden from the user. This approach would make it super-easy to add new validation pairs. Alternatively, you could create and fill some arrays in code, and use them in place of a spreadsheet table. The Looping concept using an index number remains the same. (Air code) Dim strResponse as String Set wks = Worksheets("human health csm"). Set rng = Worksheets("HiddenData").Range("ValidationData") For i = 1 to rng.Rows.Count If wks.OLEObjects(rng.cells(i,1)).Object.Value = True and wks.OLEObjects(rng.cells(i, 2)).Object.Text = "" Then strResponse = Application.InputBox(rng.Cells(i, 3), rng.Cells(i, 4)) If strResponse < "FALSE" Then wks.OLEObjects(rng.cells(i,2)).Object.Text = strResponse End If Else 'Not sure if you really want nested Ifs (which is how your code reads). 'If you really can stop all validation once the Primary If is False then: Exit For End If Next i -- HTH, George "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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simpler way for nested If statements (VBA)
Thank all of you so much for your replies!
I ended up taking elements from all of your suggestions, and came up with some code that is SO MUCH SIMPLER than what I had before, and I actually know what all of it means :) Again, thanks for your help, and my revised code is below -Hannah ----Revised Code---- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.ScreenUpdating = False If Worksheets("human health csm").Visible = True Then Worksheets("human health csm").Select End If Do Until Range("az6").Value = 5 With Worksheets("human health csm") If .SurfaceCheck6.Value = True And Len(.SurfaceSoilTextbox.Text) = 0 Then 'needs input; add to message .SurfaceSoilTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Surface Soil section.", "Please enter a transport mechanism.") Range("az1").Value = 1 If .SurfaceSoilTextbox.Value = "FALSE" Then .SurfaceSoilTextbox.Value = "" Range("az1").Value = 0 End If Else 'input provided or not needed Range("az1").Value = 1 End If If .SubSurfaceCheck3.Value = True And Len(.SubsurfaceSoilTextbox.Text) = 0 Then 'needs input; add to message .SubsurfaceSoilTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Subsurface Soil section." & vbNewLine, "Please enter a transport mechanism.") Range("az2").Value = 1 If .SubsurfaceSoilTextbox.Value = "FALSE" Then .SubsurfaceSoilTextbox.Value = "" Range("az2").Value = 0 End If Else 'input provided or not needed Range("az2").Value = 1 End If If .GroundwaterCheck5.Value = True And Len(.GroundwaterTextbox.Text) = 0 Then 'needs input; add to message .GroundwaterTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Groundwater section." & vbNewLine, "Please enter a transport mechanism.") Range("az3").Value = 1 If .GroundwaterTextbox.Value = "FALSE" Then .GroundwaterTextbox.Value = "" Range("az3").Value = 0 End If Else 'input provided or not needed Range("az3").Value = 1 End If If .SurfaceWaterCheck4.Value = True And Len(.SurfaceWaterTextbox.Text) = 0 Then 'needs input; add to message .SurfaceWaterTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Surface Water section." & vbNewLine, "Please enter a transport mechanism.") Range("az4").Value = 1 If .SurfaceWaterTextbox.Value = "FALSE" Then .SurfaceWaterTextbox.Value = "" Range("az4").Value = 0 End If Else 'input provided or not needed Range("az4").Value = 1 End If If .SedimentCheck3.Value = True And Len(.SedimentTextbox.Text) = 0 Then 'needs input; add to message .SedimentTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Sediment Water section." & vbNewLine, "Please enter a transport mechanism.") Range("az5").Value = 1 If .SedimentTextbox.Value = "FALSE" Then .SedimentTextbox.Value = "" Range("az5").Value = 0 End If Else 'input provided or not needed Range("az5").Value = 1 End If End With Loop Range("az1:az5").ClearContents Application.ScreenUpdating = True End Sub ----End---- "Hannah Lu" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simpler way for nested If statements (VBA)
As a user, I would soon learn to dislike a system with so many input boxes.
That's why I showed the technique with highlighting of the textboxes and construction of a single message box. YMMV - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Hannah Lu" wrote in message ... Thank all of you so much for your replies! I ended up taking elements from all of your suggestions, and came up with some code that is SO MUCH SIMPLER than what I had before, and I actually know what all of it means :) Again, thanks for your help, and my revised code is below -Hannah ----Revised Code---- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.ScreenUpdating = False If Worksheets("human health csm").Visible = True Then Worksheets("human health csm").Select End If Do Until Range("az6").Value = 5 With Worksheets("human health csm") If .SurfaceCheck6.Value = True And Len(.SurfaceSoilTextbox.Text) = 0 Then 'needs input; add to message .SurfaceSoilTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Surface Soil section.", "Please enter a transport mechanism.") Range("az1").Value = 1 If .SurfaceSoilTextbox.Value = "FALSE" Then .SurfaceSoilTextbox.Value = "" Range("az1").Value = 0 End If Else 'input provided or not needed Range("az1").Value = 1 End If If .SubSurfaceCheck3.Value = True And Len(.SubsurfaceSoilTextbox.Text) = 0 Then 'needs input; add to message .SubsurfaceSoilTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Subsurface Soil section." & vbNewLine, "Please enter a transport mechanism.") Range("az2").Value = 1 If .SubsurfaceSoilTextbox.Value = "FALSE" Then .SubsurfaceSoilTextbox.Value = "" Range("az2").Value = 0 End If Else 'input provided or not needed Range("az2").Value = 1 End If If .GroundwaterCheck5.Value = True And Len(.GroundwaterTextbox.Text) = 0 Then 'needs input; add to message .GroundwaterTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Groundwater section." & vbNewLine, "Please enter a transport mechanism.") Range("az3").Value = 1 If .GroundwaterTextbox.Value = "FALSE" Then .GroundwaterTextbox.Value = "" Range("az3").Value = 0 End If Else 'input provided or not needed Range("az3").Value = 1 End If If .SurfaceWaterCheck4.Value = True And Len(.SurfaceWaterTextbox.Text) = 0 Then 'needs input; add to message .SurfaceWaterTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Surface Water section." & vbNewLine, "Please enter a transport mechanism.") Range("az4").Value = 1 If .SurfaceWaterTextbox.Value = "FALSE" Then .SurfaceWaterTextbox.Value = "" Range("az4").Value = 0 End If Else 'input provided or not needed Range("az4").Value = 1 End If If .SedimentCheck3.Value = True And Len(.SedimentTextbox.Text) = 0 Then 'needs input; add to message .SedimentTextbox.Value = Application.InputBox("Please enter a transport mechanism for the Sediment Water section." & vbNewLine, "Please enter a transport mechanism.") Range("az5").Value = 1 If .SedimentTextbox.Value = "FALSE" Then .SedimentTextbox.Value = "" Range("az5").Value = 0 End If Else 'input provided or not needed Range("az5").Value = 1 End If End With Loop Range("az1:az5").ClearContents Application.ScreenUpdating = True End Sub ----End---- "Hannah Lu" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IF statements | Excel Discussion (Misc queries) | |||
| Nested If Statements | Excel Worksheet Functions | |||
| Nested if statements | Excel Worksheet Functions | |||
| Nested if statements | Excel Worksheet Functions | |||
| Nested If statements | Excel Worksheet Functions | |||
Linear Mode
