Case Select statement not firing macro

Question

I have a private sub macro within a tab called "Model" that I'm hoping will hide and unhide sheets and columns based on a concatenated formula value in cell O1 of the model tab. However, when I make a change on the worksheet, the macro doesn't appear to do what I'd like it to. Below is my code, and I'd really appreciate any assistance anyone can provide. I'm pretty novice when it comes to VBA.

Private Sub Worksheet_change(ByVal target As Range)

Worksheets("Model").Range("O1").Calculate
Dim test As String
test = Worksheets("Model").Range("O1").Value
Select Case test
    Case "", "new", "credit", "Non-Credit", "Existing"
        Exit Sub
    Case "Non-CreditNew"
        Sheets("Enrollment Waterfall-Credit-New").Visible = False
        Sheets("Enrollment Waterfall-New").Visible = True
        Sheets("Enrollment Waterfall-Credit-Exs").Visible = False
        Columns("Z:AR").EntireColumn.Hidden = True
        Columns("AT:BH").EntireColumn.Hidden = False
        Columns("F:H").EntireColumn.Hidden = True
    Case "Non-CreditExisting"
        Sheets("Enrollment Waterfall-Credit-New").Visible = False
        Sheets("Enrollment Waterfall-New").Visible = True
        Sheets("Enrollment Waterfall-Credit-Exs").Visible = False
        Columns("Z:AR").EntireColumn.Hidden = True
        Columns("AT:BH").EntireColumn.Hidden = False
        Columns("F:H").EntireColumn.Hidden = False
    Case "CreditNew"
        Columns("F:H").EntireColumn.Hidden = True
        Sheets("Enrollment Waterfall-Credit-New").Visible = True
        Sheets("Enrollment Waterfall-New").Visible = False
        Sheets("Enrollment Waterfall-Credit-Exs").Visible = False
        Columns("Z:AR").EntireColumn.Hidden = False
        Columns("AT:BH").EntireColumn.Hidden = True
    Case "CreditExisting"
        Sheets("Enrollment Waterfall-Credit-New").Visible = False
        Sheets("Enrollment Waterfall-New").Visible = False
        Sheets("Enrollment Waterfall-Credit-Exs").Visible = True
        Columns("Z:AR").EntireColumn.Hidden = False
        Columns("AT:BH").EntireColumn.Hidden = False
        Columns("F:H").EntireColumn.Hidden = False
    End Select
End Sub

Neither one of these worked to hide or unhide the columns and/or sheets. Am I missing something else? — Aubrey Dubberke, May 12 '20 at 00:34
What "change" are you making? Seems like the `Worksheet_Calculate` event might be more appropriate (?) — BigBen, May 12 '20 at 02:30
@BigBen--I have a data entry table where people enter information. I only want to macro to fire when people have completed entering the information, so I made "lookup" cell in O1 that concatenates the necessary values in the data entry fields. If the values in O1 meet certain criteria, I want the macro to fire off and hide/unhide columns and pages. I have this as a change event because I don't want it to fire off every time a calculation occurs on this tab. It's mostly formula driven with little data entry except for the table. — Aubrey Dubberke, May 12 '20 at 12:08
@BigBen--I'm happy to upload my file if you'd like to see it. Like I said, I'm pretty novice at VBA so the code is most likely incredibly inelegant. — Aubrey Dubberke, May 12 '20 at 16:05
@AubreyDubberke 1.) Change `Dim test As Variant` to `Dim test As String` 2.) Remove `test = ` from all your case statements — jblack, May 12 '20 at 22:28
@jblack--I've updated the code to the above. Still not hiding and unhiding. Any thoughts? Thanks! — Aubrey Dubberke, May 13 '20 at 02:32
@AubreyDubberke You've changed your case statements. They should be as they previously were, only, instead of writing `Case test = "new"` you should just say `Case "new"`. Apply that same format to all your case statements and it will work — jblack, May 13 '20 at 02:56
@jblack...still not working...Is there anyway to upload the file on here for you to see it for yourself? If you're interested in doing that. — Aubrey Dubberke, May 13 '20 at 12:03
@jblack--I made your changes. Still nothing. Could something be broken in the file? I'm stumped. — Aubrey Dubberke, May 14 '20 at 12:08
@jblack--nevermind. Classic IT problem. I had to shut my computer off and turn it back on again. Once I did that, it worked. Thanks so much for your help and patience! — Aubrey Dubberke, May 14 '20 at 12:22

Case Select statement not firing macro

0 Answers0