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

Answers


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

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 :)


Need Your Help

C++, Class redefinition

c++ class redefinition

Error 3 error C2011 : 'Queue' : 'class' type redefinition.

How to define subsumptions in prolog lists

prolog

To do unification in Prolog, we write: