Solved How to add/remove specific rows based on input options.

Microsoft Office excel 2003
December 15, 2016 at 00:11:40
Specs: Windows 7
I am really desperately hoping someone can help me with this.

Sheet 1

Cell C3: Name
Cell D3: User-Input

Cell C4: Children
Cell D4: Option of either "Yes" or "No"

If D4 is Yes, then this pops up;
Cell C5: Number of Children
Cell D5: Option of "1" to "10"

If D5 is 3, then this pops up;
Cell C7: Child 001
Cell C8: Name
Cell C9: Birthday
Cell C10: Age
Cell C12: Child 002
Cell C13: Name
Cell C14: Birthday
Cell C15: Age
Cell C17: Child 003
Cell C18: Name
Cell C19: Birthday
Cell C20: Age

if D4 is No, then nothing changes.
if D4 is Yes, and the things below popped up. Changing it back to No, will remove it.

if D5 is 3, and the things below popped up. Changing D4 to No, will remove it also.


See More: How to add/remove specific rows based on input options.

Report •

#1
December 28, 2016 at 23:06:33
Doesn't look like anyone wants to help you, it's been two weeks since you posted.
Maybe nobody knows.

Report •

#2
December 29, 2016 at 09:32:01
✔ Best Answer
I'm pretty sure this accomplishes the requirements set forth in the OP.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check value in Yes-No Drop Down
   If Target.Address = "$D$4" Then
    Application.EnableEvents = False
'If No, Clear all Child related data
     If Target = "No" Then
        Range("$C$5:$D$55").ClearContents
        Range("D5").Validation.Delete
        Application.EnableEvents = True
        Exit Sub
'If Yes, Populate D5 with Drop Down
     Else
        Range("C5") = "Number Of Children:"
        Range("D5") = "Please Select Number"
           With Range("D5").Validation
             .Delete
             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="1,2,3,4,5,6,7,8,9,10"
             .IgnoreBlank = True
             .InCellDropdown = True
           End With
        Application.EnableEvents = True
        Exit Sub
     End If
   End If
'Check number of Children selected in Drop Down
   If Target.Address = "$D$5" Then
        Application.EnableEvents = False
          If IsNumeric(Target) Then
           Range("$C$7:$D$55").ClearContents
'Initialize Row Counter
            rwNum = 6
              For numKids = 1 To Range("D5")
                   Range("C" & numKids + rwNum) = "Child 00" & numKids
                   Range("C" & numKids + rwNum + 1) = "Name"
                   Range("C" & numKids + rwNum + 2) = "Birthday"
                   Range("C" & numKids + rwNum + 3) = "Age"
'Update Row Counter for next Child
                     rwNum = rwNum + 4
              Next
'Fix Child 0010 to read 010
                If Range("D5") = 10 Then _
                  Range("C" & numKids + rwNum - 5) = "Child 010"
          End If
   End If
  Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •
Related Solutions


Ask Question