Using a combo box to change sheet view

%3CLINGO-SUB%20id%3D%22lingo-sub-1757124%22%20slang%3D%22en-US%22%3EUsing%20a%20combo%20box%20to%20change%20sheet%20view%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1757124%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%20I%20have%20done%20this%20before%20but%20I%20can%20not%20remember%20how%20to%20do%20it%20now.%26nbsp%3B%20I%20have%20added%20a%20Combo%20box%20to%20my%20worksheet%20and%20I%20want%20the%20user%20input%20in%20that%20sheet%20to%20change%20the%20what%20columns%20will%20be%20shown%20on%20that%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20combo%20box%20is%20in%2C%20linked%20to%20the%20options%20list%20and%20the%20linked%20cell.%26nbsp%3B%20It%20is%20the%20next%20step%2C%20taking%20the%20varabale%20imput%20and%20change%20the%20columns%20that%20are%20visable%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1757124%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1757286%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20combo%20box%20to%20change%20sheet%20view%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1757286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824355%22%20target%3D%22_blank%22%3E%40meathie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESince%20you%20have%20not%20shared%20any%20sample%20data%20or%20column%20to%20hide%2Funhide%20with%20us%20therefore%20I%20would%20like%20to%20share%20few%20VBA%20codes%2C%2C%20help%20you%20to%20fix%20the%20issue.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20ComboBox1_Change()%0A%0ASelect%20Case%20ComboBox1.Value%0A%0A%20%20Case%20%221%20Column%22%0A%20%20%20%20Columns(%22A%22).Hidden%20%3D%20True%0A%20%20%20%20%0A%20%20Case%20%222%20Column%22%0A%20%20%20%20Columns(%22A%3AB%22).Hidden%20%3D%20True%0A%0A%20%20Case%20%223%20Column%22%0A%20%20%20%20Columns(%22A%3AC%22).Hidden%20%3D%20True%0A%0A%20%20Case%20%220%20Column%22%0A%20%20%20%20Columns(%22A%3AC%22).Hidden%20%3D%20False%0A%20%0AEnd%20Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAnother%20is%20%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ComboBox1_Change()%0A%20%20%20%20If%20Range(%22%24A%241%22).Value%20%3D%20%22Yes%22%20Then%0A%20%20%20%20%20%20%20%20Range(%22A%3AC%2CF%3AG%22).EntireColumn.Hidden%20%3D%20True%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20If%20Range(%22%24A%241%22).Value%20%3D%20%22No%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%3AC%2CF%3AG%22).EntireColumn.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3E%3CEM%3EAdjust%20columns%20to%20hide%2Funhide%20as%20needed.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1770378%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20combo%20box%20to%20change%20sheet%20view%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1770378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20getting%20back%20to%20me.%26nbsp%3B%20I%20really%20appreciate%20it.%26nbsp%3B%20This%20is%20what%20i%20have%20done%20with%20you%20suggestion%20so%20far%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20week%20I%20add%20a%20new%20sheet%20for%20that%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20ComboBox1_Change()%3C%2FP%3E%3CP%3ESelect%20Case%20ComboBox1.Value%3C%2FP%3E%3CP%3ECase%20%221%20Column%22%3CBR%20%2F%3EColumns(%22A%3AAA%22).Hidden%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3ECase%20%222%20Column%22%3CBR%20%2F%3EColumns(%22F%3AAA%22).Hidden%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3ECase%20%223%20Column%22%3CBR%20%2F%3EColumns(%22C%3AF%22).Hidden%20%3D%20True%3CBR%20%2F%3EColumns(%22J%3AAA%22).Hidden%20%3D%20True%3C%2FP%3E%3CP%3ECase%20%224%20Column%22%3CBR%20%2F%3EColumns(%22C%3AJ%22).Hidden%20%3D%20True%3CBR%20%2F%3EColumns(%22O%3AAA%22).Hidden%20%3D%20True%3C%2FP%3E%3CP%3ECase%20%225%20Column%22%3CBR%20%2F%3EColumns(%22C%3AO%22).Hidden%20%3D%20True%3CBR%20%2F%3EColumns(%22S%3AAA%22).Hidden%20%3D%20True%3C%2FP%3E%3CP%3ECase%20%226%20Column%22%3CBR%20%2F%3EColumns(%22C%3AS%22).Hidden%20%3D%20True%3CBR%20%2F%3EColumns(%22W%3AAA%22).Hidden%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3ECase%20%227%20Column%22%3CBR%20%2F%3EColumns(%22C%3AW%22).Hidden%20%3D%20True%3CBR%20%2F%3EColumns(%22AA%3AAA%22).Hidden%20%3D%20True%3C%2FP%3E%3CP%3ECase%20%220%20Column%22%3CBR%20%2F%3EColumns(%22A%3AAA%22).Hidden%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

HI, I have done this before but I can not remember how to do it now.  I have added a Combo box to my worksheet and I want the user input in that sheet to change the what columns will be shown on that sheet.

 

The combo box is in, linked to the options list and the linked cell.  It is the next step, taking the varabale imput and change the columns that are visable

3 Replies
Highlighted

@meathie 

 

Since you have not shared any sample data or column to hide/unhide with us therefore I would like to share few VBA codes,, help you to fix the issue.

 

Private Sub ComboBox1_Change()

Select Case ComboBox1.Value

  Case "1 Column"
    Columns("A").Hidden = True
    
  Case "2 Column"
    Columns("A:B").Hidden = True

  Case "3 Column"
    Columns("A:C").Hidden = True

  Case "0 Column"
    Columns("A:C").Hidden = False
 
End Select
End Sub

 

Another is : 

 

Sub ComboBox1_Change()
    If Range("$A$1").Value = "Yes" Then
        Range("A:C,F:G").EntireColumn.Hidden = True
    Else
        If Range("$A$1").Value = "No" Then
            Range("A:C,F:G").EntireColumn.Hidden = False
        End If
    End If
End Sub

 

N.B.  Adjust columns to hide/unhide as needed.

 

Highlighted

@Rajesh-S 

 

Thank you so much for getting back to me.  I really appreciate it.  This is what i have done with you suggestion so far

 

Each week I add a new sheet for that week.

 

Private Sub ComboBox1_Change()

Select Case ComboBox1.Value

Case "1 Column"
Columns("A:AA").Hidden = False

Case "2 Column"
Columns("F:AA").Hidden = True

Case "3 Column"
Columns("C:F").Hidden = True
Columns("J:AA").Hidden = True

Case "4 Column"
Columns("C:J").Hidden = True
Columns("O:AA").Hidden = True

Case "5 Column"
Columns("C:O").Hidden = True
Columns("S:AA").Hidden = True

Case "6 Column"
Columns("C:S").Hidden = True
Columns("W:AA").Hidden = True

Case "7 Column"
Columns("C:W").Hidden = True
Columns("AA:AA").Hidden = True

Case "0 Column"
Columns("A:AA").Hidden = False

End Select
End Sub

Highlighted
Glad to help you,,, since what I've suggested is working then if you wish please mark my post as Best solution as well like.

And, keep asking ☺