Oct 07 2020 09:56 PM
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
Oct 07 2020 11:07 PM
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.
Oct 12 2020 12:04 AM
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
Oct 12 2020 05:43 AM