Help with Conditional HideIf

[pann8888]

Hello-

I have searched the forum, but have not been able to find this exact question. Found a bunch of related questions, but wasn't talented enough to translate them to solve my problem. =)

I would like to conditionally autohide columns in response to a changes in the value of a single cell. For example:

If B2 = 1, hide (F:AQ)
If B2 = 2, hide (H:AQ)
If B2 = 3, hide (J:AQ)
If B2 = 4, hide (L:AQ)
IF B2 = 5, hide (N:AQ)

...and so on. I've been able to make this work using b2 = 1, but when I try to add anything else, I run into trouble. I also need it to be stable, such that if B2 is changed from 1 to 2, say, the whole thing doesn't fall apart. If B2 is changed from 1 to 2, columns F:AQ should stay visible, for example. Any help would be welcome.

 

[tigeravatar]

pann8888,

Your post confuses me because it contradicts itself:

If B2 = 1, hide (F:AQ)
If B2 = 2, hide (H:AQ)
If B2 = 3, hide (J:AQ)
If B2 = 4, hide (L:AQ)
IF B2 = 5, hide (N:AQ)

...

If B2 is changed from 1 to 2, columns F:AQ should stay visible

In the first part you want to hide the columns, in the second part they should "stay visible", what are you looking to do?

 

[pann8888]

My apologies. What I am trying to create is a survey data collection form. On sheet 1, I will collect biographical information from participants. The number in B2 is merely a count of the number of participants. Sheet 2 contains columns that the participants will use to give 2 numerical ratings (in columns D and E) to statements in column A.

I am trying to set up the sheet such that only the appropriate number of columns is displayed. If I have ratings from only one participant, I want to hide all of the other response columns.

 

[tigeravatar]

pann8888,

Here's one way

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngChg As Range
    Dim SurveyParticipants As Long
    
    Set rngChg = Intersect(Target, Range("B2"))
    
    If Not rngChg Is Nothing Then
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Select Case IsNumeric(rngChg.Value)
            Case True:  SurveyParticipants = WorksheetFunction.Max(1, CLng(rngChg.Value))
            Case False: SurveyParticipants = 1
        End Select
        Range("F:AQ").EntireColumn.Hidden = False
        If SurveyParticipants < 20 Then Range(Columns("F").Offset(, (SurveyParticipants - 1) * 2), Columns("AQ")).EntireColumn.Hidden = True
        
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        
    End If
    
End Sub

 

[pann8888]

Thank you - that worked perfectly!