"Select Case True" - adding more criterias

Question

I have a code below that stopped working after I've added this new bit:

Select Case True
    Case Range("B16") = "High Risk"
        Rows("5:12").EntireRow.Hidden = False
        Rows("24").EntireRow.Hidden = False
    Case Range("B16") = "Medium Risk"
        Rows("5:12").EntireRow.Hidden = True
        Rows("24").EntireRow.Hidden = True
    Case Range("B16") = "Standard Risk"
        Rows("5:12").EntireRow.Hidden = True
        Rows("24").EntireRow.Hidden = True
    End Select

FULL CODE:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.ScreenUpdating = False

    Select Case True
    Case Range("B14") = "Medium Risk"
        Rows("5:12").EntireRow.Hidden = True
        Rows("24").EntireRow.Hidden = True
    Case Range("B14") = "High Risk"
        Rows("5:12").EntireRow.Hidden = False
        Rows("24").EntireRow.Hidden = False
    End Select

Select Case True
    Case Range("XES72") = "Medium Risk"
        Rows("5:12").EntireRow.Hidden = True
        Rows("24").EntireRow.Hidden = True
    Case Range("XES72") = "High Risk"
        Rows("5:12").EntireRow.Hidden = False
        Rows("24").EntireRow.Hidden = False
    End Select

Select Case True
    Case Range("B16") = "High Risk"
        Rows("5:12").EntireRow.Hidden = False
        Rows("24").EntireRow.Hidden = False
    Case Range("B16") = "Medium Risk"
        Rows("5:12").EntireRow.Hidden = True
        Rows("24").EntireRow.Hidden = True
    Case Range("B16") = "Standard Risk"
        Rows("5:12").EntireRow.Hidden = True
        Rows("24").EntireRow.Hidden = True
    End Select

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Any advice how to make sure that the "new" part I have added is also considered by the VBA?

The part which works is this (and everything above it):

Select Case True
    Case Range("B16") = "High Risk"
        Rows("5:12").EntireRow.Hidden = False
        Rows("24").EntireRow.Hidden = False

Then, if I change B16 back to "Medium Risk" (or "Standard Risk") it simply doesn't hide the rows that it supposed to:

 Case Range("B16") = "Medium Risk"
            Rows("5:12").EntireRow.Hidden = True
            Rows("24").EntireRow.Hidden = True
 Case Range("B16") = "Standard Risk"
            Rows("5:12").EntireRow.Hidden = True
            Rows("24").EntireRow.Hidden = True
        End Select

Set a breakpoint at Application.EnableEvents = False, run your code step by step (press F8 line-by-line), and tell us what happened, where did the actual program run differ from your expectation. — z32a7ul, Dec 08 '17 at 09:57
Try to write your business logic for yourself on a piece of paper. E.g. I want to hide row `5:12` if Range("B16") is with "High Risk" or Range("B14") is with "Medium Risk". Read it 2 times and put it as bullets to your question! :) — Vityata, Dec 08 '17 at 09:57
To answer your earlier question Kazimierz, I do not get any errors. Simply part of the code is not working (refreshing). — Alien_Explorer, Dec 08 '17 at 10:03
just to confirm- do you remember that text comparison is `case sensitive` by default in vba? — Kazimierz Jawor, Dec 08 '17 at 10:05
When you go through your code step-by-step, does it step on Case ... = "Medium Risk"? After that, does it step on the line Rows("5:12")...? I asked this because the bug can be caused by either a wrong condition or a wrong command. — z32a7ul, Dec 08 '17 at 11:37
So when I go through it step by step, it approaches B16 and when it is "High Risk" it will unhide the rows (which is great). But when I will change B16 (manually from the dropdown menu) to e.g. "Medium Risk" it will not hide the rows (and it should). — Alien_Explorer, Dec 08 '17 at 13:30
Are you sure that your code runs after changing to Medium Risk? Did you set the breakpoints in your code? — z32a7ul, Dec 08 '17 at 13:38
So the whole concept of Private Sub Worksheet_Calculate() is to be able to refresh things as they change on the worksheet (e.g. with dropdown menus), right? PS. Breakpoint? — Alien_Explorer, Dec 08 '17 at 13:42
Go to the VBA Editor, click on the line, where you want the breakpoint, e.g. the first one. Press F8, a red circle left to the code will appear. When your macro runs, and gets to that line, it will stop, and you will be able to see, what exactly it does. There is a link, how it works: https://www.wiseowl.co.uk/blog/s196/breakpoints.htm Its essential for debugging. — z32a7ul, Dec 08 '17 at 13:55
Pretty sure it wasn't downvoted for being too difficult. The whole concept of `Private Sub Workwsheet_Calculate()` is to run code when things _calculate_ on the worksheet - a dropdown menu doesn't fire a calculation. The whole point of a `Private Sub Worksheet_Change(ByVal Target As Range)` is to run code when things _change_ on your worksheet. — Darren Bartrup-Cook, Dec 08 '17 at 15:55

score 4 · Accepted Answer · answered Dec 08 '17 at 09:34

4

Your Select Case statements should be constructed a bit differently. Instead of this logic:

Select Case True
    Case Range("B16") = "High Risk"
    ...
    ...

you should use this logic:

Select Case Range("B16")  '<< check the value of cell B16 ...
    Case "High Risk"      '<< ... in case it equals 'High Risk'...
    ...                   '<< ... do this
    ...

answered Dec 08 '17 at 09:34

Kazimierz Jawor

18,861
7
35
55

Hi, it's not working - it's the same as it was in my intial case. I don't get any errors. The issue is that Private Sub Worksheet_Calculate() should update the spreadsheet automatically after B16 changes its value and it doesn't. Instead I have to click F5 in the module in order for it to work on B16. It wasn't the case before I've added the "new" code (see above). – Alien_Explorer Dec 08 '17 at 09:51
are you sure you are using correct event, shouldn't that be `Worksheet_Change()` instead? To trigger `Worksheet_Calculation()` event you have to have some formula in your worksheet, do you have one? It seems that code is working fine but the logic of events is broken here. – Kazimierz Jawor Dec 08 '17 at 09:56
I already have Worksheet_Change() working on the previous sheet. Hence for this one, I'm simply applying Sub Worksheet_Calculate(). I will update my question. – Alien_Explorer Dec 08 '17 at 09:59
1

I don't think this was a solution to the posters problem. Select Case True is a valid structure; there was a post regarding that on SO, too: https://stackoverflow.com/questions/794036/select-case-true. – z32a7ul Dec 08 '17 at 13:32
1

@z32a7ul It is a valid construct, but I don't think it's needed in this case. This answer makes more sense than `Select Case True`. As the accepted answer on your link said: _Just take care when you use it, because it's not a "standard programming construct"_. I guess that construct works on `Range("B16") = "High Risk"` will return TRUE/FALSE. If it's true it will match the `Select Case`. The more usual way is as in this answer. Either way, the event won't fire if there's nothing to calculate - better to use `Worksheet_Change` event. – Darren Bartrup-Cook Dec 08 '17 at 15:51
Also, `B16` check could be combined as `Case "High Risk"...... Case "Medium Risk", "Standard Risk"` – Darren Bartrup-Cook Dec 08 '17 at 16:01

"Select Case True" - adding more criterias

1 Answers1