Use Macro to Unhide Rows

[JKD_Excel]

I’m new to macros and am NOT a programmer. I can use the “Record Macro” function and assign to option buttons to either hide or unhide pretty easily, but I’m struggling to get it to do what I want.

I am trying to create a series of yes/no questions where the answer to the first question determines what question is revealed next, and the answer to that question dictates what question is revealed next. I’m trying to accomplish this by starting with most rows hidden and then unhiding certain rows with each answer using macros assigned to each option button.

I’ve tried using a text button labeled “refresh” to hide all but the first question, say rows 6-100. This would be the starting point for the end-user, but also give them the ability to start over (at least that’s the intent). I want row 6 to appear (unhide) if the first question was answered with a no and row 7 to appear (unhide) if the first question was answered with a yes. And so on...

It’s not working as intended. Any suggestions on how to make this work using macros or by utilizing another function in excel? I’d like the end result to show the series of questions that were presented and answered without blank (or seemingly blank) rows in between where questions were not applicable, so I don’t think conditional formatting is an option...

 

[Moses99]

create multiple sub procedure for each question.

then lastly use if , else, elseif, end if. That should do the trick.

 

[Yongle]

One way to approach this is to use a (hidden) helper column ("H")

- each cell H5:H100 is either empty or Y (= hide this row)
- initially the values in H6:H100 are set to Y
- as each question is answered use VBA to amend values in column H (using Worksheet_Change event) - see below
- then a simple macro hides \ unhides as required

Assuming that the helper column is column H ...

Sub HideUnhide()
    Dim rngHide As Range, rngUnhide As Range, cel As Range
    Set rngHide = Range("H5:H100")
    Set rngUnhide = Range("H4")                 'any permenantly visible cell above H5
    rngHide.EntireRow.Hidden = True
    For Each cel In rngHide
        If cel <> "Yes" Then Set rngUnhide = Union(rngUnhide, cel)
    Next
    rngUnhide.EntireRow.Hidden = False
End Sub

Worksheet_Change
- answering each question needs to drive Worksheet_Change event
eg if the answer to first question is found in D5

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("H")) Is Nothing Then Exit Sub    
    Const Y = "Y", B = ""
    Dim v As Variant:   v = LCase(Target.Value)
    
    Select Case Target.Address(0, 0)
        Case "D5"
            If v = "no" Then
                [H6] = B
            ElseIf v = "yes" Then
                [H7] = B
            End If
        Case "D6"
            '... etc
    End Select
    
    Call HideUnhide
End Sub

Code required for EVERY question to delete the "Y" flag in column H
- is there a pattern (or several patterns) that can be used to reduce coding ?
- eg instead of hard coding [H6] could use Target.Offset(1,4) where the same pattern applies to several answers

   Case "D5", "D10", "D20"
            If v = "no" Then
                Target.Offset(1,4) = B
            ElseIf v = "yes" Then
                Target.Offset(1,5) = B
            End If

If you need help with the VBA driving values in column H, then
- provide details of what should happen when the first 6 questions are answered
- for each question I need to know
1. which answers are possible
2. where the values are entered
3. which rows are hidden and \ or made visible as a result

and if there are more complicated questions affecting more than 2 rows, provide an example

 

[Yongle]

Oops, I forgot to amend something in Sub HideUnhide

If cel <> "Yes" Then Set rngUnhide = Union(rngUnhide, cel)

should be

If cel <> "[COLOR=#ff0000]Y[/COLOR]" Then Set rngUnhide = Union(rngUnhide, cel)