ComboBox Linked Cell change does not trigger hiding/unhiding of columns

I'm using an Active X combobox ("GLC_Code") to reference a list of staff codes and have assigned a cell ("D1") to match whatever is selected in the combobox. Based on what is in "D1" different columns should hide/unhide. Except they don't....However, if I make a change in the combobox then double click in the (also changed/matching) linked cell and then double click in any other cell then the hiding/unhiding event is triggered, as scripted. I'm pretty sure I'm overlooking something simple but the neurons aren't firing well today. Here is my code:

Option Explicit

Private Sub GLC_Code_Change()

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
    If Target.Address = "$D$1" Then
        Select Case Target.Value
        Case Is = "Designer GLC 1"
            Range("B:B").EntireColumn.Hidden = False
            Range("C:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 2"
            Range("C:C").EntireColumn.Hidden = False
            Range("B:B").EntireColumn.Hidden = True
            Range("D:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 3"
            Range("D:D").EntireColumn.Hidden = False
            Range("B:C").EntireColumn.Hidden = True
            Range("E:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 4"
            Range("E:E").EntireColumn.Hidden = False
            Range("B:D").EntireColumn.Hidden = True
            Range("F:R").EntireColumn.Hidden = True
       End Select
        Application.ScreenUpdating = True
    End If
End Sub

Also, I tried several configurations to list the series of columns in one line but nothing worked. Suggestions?


Thanks for your help. David

I don't understand, sorry. I reviewed the third post from the link you forwarded but it didn't seem to fit exactly what I am (trying) to do. My problem is that nothing happens (no columns hide/unhide) when the content (text, not numerical values) of the cell linked to a ComboBox changes (when a different listing in that Combobox is selected.) If I double click in the linked cell and then double click in any other cell, then the hiding/unhiding works. Specifically, what am I doing wrong with my code, please?

I'm sure this is a very basic question but I've searched for several hours online and have found nothing that works. I'm very frustrated...

Thanks for your help. David

Hi David,

I did all that I could with the code you provided. For additional help, you're going to have to upload a sample workbook, that contains your code in context.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Lewis

Here is the document. It's pretty brief. Note the location of the cell is, purely by coincidence, "U2".... not "D1" as indicated in my original inquiry.

Thanks for your help Lewis. I really appreciate it. David

Hi David,

Try this in the Scorecard_New module. It worked for me. If it doesn't work, we have to:
a. Verify the properties in the Combobox (i.e. linked cell, and make sure it triggers the GlC_Code_Change() event()).
b. Make sure the Sheet isn't corrupt.

The Worksheet_Change() event is not triggered when 'U2' changes, because a formula changes the value of 'U2'. However, when the ComboBox changes value, the GLC_Code_Change() event is triggered. I had to make minimal changes to make it work. Changes in red.

Option Explicit

Private Sub GLC_Code_Change()
  Debug.Print "GLC_Code_Change at " & Now
        Application.ScreenUpdating = False
        Select Case GLC_Code.Value
        Case Is = "Designer GLC 1"
            Range("B:B").EntireColumn.Hidden = False
            Range("C:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 2"
            Range("C:C").EntireColumn.Hidden = False
            Range("B:B").EntireColumn.Hidden = True
            Range("D:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 3"
            Range("D:D").EntireColumn.Hidden = False
            Range("B:C").EntireColumn.Hidden = True
            Range("E:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 4"
            Range("E:E").EntireColumn.Hidden = False
            Range("B:D").EntireColumn.Hidden = True
            Range("F:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 5"
            Range("F:F").EntireColumn.Hidden = False
            Range("B:E").EntireColumn.Hidden = True
            Range("G:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 6"
            Range("G:G").EntireColumn.Hidden = False
            Range("B:F").EntireColumn.Hidden = True
            Range("H:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 7"
            Range("H:H").EntireColumn.Hidden = False
            Range("B:G").EntireColumn.Hidden = True
            Range("I:R").EntireColumn.Hidden = True
        Case Is = "Designer GLC 8"
            Range("I:I").EntireColumn.Hidden = False
            Range("B:H").EntireColumn.Hidden = True
            Range("J:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 1"
            Range("J:J").EntireColumn.Hidden = False
            Range("B:I").EntireColumn.Hidden = True
            Range("K:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 2"
            Range("K:K").EntireColumn.Hidden = False
            Range("B:J").EntireColumn.Hidden = True
            Range("L:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 3"
            Range("L:L").EntireColumn.Hidden = False
            Range("B:K").EntireColumn.Hidden = True
            Range("M:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 4"
            Range("M:M").EntireColumn.Hidden = False
            Range("B:L").EntireColumn.Hidden = True
            Range("N:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 5"
            Range("N:N").EntireColumn.Hidden = False
            Range("B:M").EntireColumn.Hidden = True
            Range("O:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 6"
            Range("O:O").EntireColumn.Hidden = False
            Range("B:N").EntireColumn.Hidden = True
            Range("P:R").EntireColumn.Hidden = True
        Case Is = "Engineer GLC 7"
            Range("P:P").EntireColumn.Hidden = False
            Range("B:O").EntireColumn.Hidden = True
            Range("Q:R").EntireColumn.Hidden = True
        Case Is = "Engineer PLDL"
            Range("Q:Q").EntireColumn.Hidden = False
            Range("B:P").EntireColumn.Hidden = True
            Range("R:R").EntireColumn.Hidden = True
        End Select
        Application.ScreenUpdating = True
End Sub

Lewis

Shazam! That did it. Thanks for your Lewis. You rock. David