CODEWITHSUNDEEP
vbaexcelcombobox
phentrus
phentrus

Reputation: 29

Hide Columns Based on Combobox (Form) In Excel

I have everything setup with different options for forecasts. I have done this in the past at a different job and I can't figure out where I am going wrong.

Private Sub ComboBox1_Change()

  If ComboBox1.Value = "2 Weeks" Then
      Columns("J:L").Select
      Selection.EntireColumn.Hidden = False
      Columns("M:R").Select
      Selection.EntireColumn.Hidden = True
  End If

  If ComboBox1.Value = "6 Weeks" Then
      Columns("M:O").Select
      Selection.EntireColumn.Hidden = False
      Columns("J:L").Select
      Selection.EntireColumn.Hidden = True
      Columns("P:R").Select
      Selection.EntireColumn.Hidden = True
  End If

  If ComboBox1.Value = "12 Weeks" Then
      Columns("P:R").Select
      Selection.EntireColumn.Hidden = False
      Columns("J:O").Select
      Selection.EntireColumn.Hidden = True
  End If

End Sub

Upvotes: 0

Views: 3853

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

In the title I included it was a form control. – phentrus 8 mins ago

The reason why I was confused and asked you in the comments above whether you are using a form control or an ActiveX control is because your quesiton title says "Form" but the code is for ActiveX.

For Form Control, paste this code in a module. 

Option Explicit

Sub DropDown1_Change()
    Dim DDown As Shape

    Set DDown = ActiveSheet.Shapes(Application.Caller)

    Select Case DDown.ControlFormat.List(DDown.ControlFormat.ListIndex)
        Case "2 Weeks"
            Columns("J:L").Hidden = False
            Columns("M:R").Hidden = True
        Case "6 Weeks"
            Columns("J:L").Hidden = True
            Columns("M:O").Hidden = False
            Columns("P:R").Hidden = True
        Case "12 Weeks"
            Columns("J:O").Hidden = True
            Columns("P:R").Hidden = False
    End Select
End Sub

Next right click your Form Combobox and assign the above macro to it :)

Upvotes: 3

Lance Roberts
Lance Roberts

Reputation: 22842

It seems like your code should work just fine. I rewrote it to be more concise and it worked perfectly (with an Active-X control).

Private Sub ComboBox1_Change()

Select Case ComboBox1.Value
  Case "2 Weeks"
    Columns("J:L").Hidden = False
    Columns("M:R").Hidden = True
  Case "6 Weeks"
    Columns("J:L").Hidden = True
    Columns("M:O").Hidden = False
    Columns("P:R").Hidden = True
  Case "12 Weeks"
    Columns("J:O").Hidden = True
    Columns("P:R").Hidden = False
End Select

End Sub

Upvotes: 3

Related Questions