I have a sheet, named "Instructions" where people can choose the number of activities they participate in (From 1-25). This is in cell H18

I want to be able to hide columns in sheet "InputSheet" based on their selection. So for example, if they choose "3" activities, I need to be able to hide columns F:AA. If they choose 10 activities, I need to hide columns M:AA. The columns I need to hide are from D:AA.

I have looked through numerous forums but only find ways to hide rows or "YES", "No" options. Not multiple like this. Any help is appreciated!

3 Answers

1
Scott Craner On Best Solutions

Quick Loop

Sub Hide
    Worksheets("InputSheet").Columns("D:AA").Hidden = False
    Dim i as long
    For i = Worksheets("Instructions").Range("H18") + 4 to 27
         Worksheets("InputSheet").Columns(i).Hidden = True
    Next i
End Sub

Or no loop:

Sub Hide
    Worksheets("InputSheet").Columns("D:AA").Hidden = False
    With Worksheets("InputSheet")
        .Range(.Cells(1,Worksheets("Instructions").Range("H18").Value + 4),.Cells(1,27)).EntireColumn.Hidden = True
    End With
 End Sub
0
Error 1004 On

You may try this as a first step:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Value As Long
    Dim ColumnsToHide As String

    If Not Intersect(Target, ThisWorkbook.Worksheets("Instructions").Range("H18")) And Target.Count = 1 Then

        Value = Target.Value

        Select Case Value

            Case Is = 3
                ColumnsToHide = "F:AA"
            Case Is = 10
                ColumnsToHide = "M:AA"

        End Select

        Application.EnableEvents = False

            With ThisWorkbook.Worksheets("InputSheet")
                .Cells.EntireColumn.Hidden = False
                .Columns(ColumnsToHide).EntireColumn.Hidden = True
            End With

        Application.EnableEvents = True

    End If

End Sub
0
Asger On

Place this within the code module of your worksheet "Instructions".

Every time you change cell H18 between 1 and 10, it hides the wanted range in the other sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NumberOfActivities As Long
    If Not Intersect(Target, Me.Range("H18")) Is Nothing Then
        Application.EnableEvents = False
        NumberOfActivities = CLng(Me.Range("H18").Value)
        If NumberOfActivities >= 1 And NumberOfActivities <= 10 Then
            With Sheets("InputSheet")
                .Columns("D:AA").Hidden = False
                .Range( _
                    .Range("D1").Offset(0, NumberOfActivities - 1), _
                    .Range("AA1")).EntireColumn.Hidden = True
            End With
        End If
        Application.EnableEvents = True
    End If
End Sub