Multiple If Statements Being Slow - Is there a different way to write this code?

    Hi All,


    I'd be grateful if someone can help me out with this.


    I currently have the code below, but when an if statement is triggered the sheet loads for 5 seconds. Is there another way to write this code so it's more efficient?



    Thanks very much!

    this is how I would do it (there are other ways to do this)


    Hi rabsofty,


    Thank you very much for the response.


    I forgot about this method. It's definitely tidier and easier to read, functions the same, but still has between 5 and 6 seconds of load time. I'll see if I can reduced this.

    Storm,


    on my machine it's almost instant.
    can you provide me with your spreadsheet? (without sensitive data)

    Your code takes a few seconds on my system, rabsofty.


    This works in about 1 second for me, time taken will vary depending on the number of sheets that need to be hidden or made visible each time the code fires. The first time you run it code run time may be 2-3 seconds, after that should be less than a second.


    When pasting this code to your worksheet object module make sure the "Option Base 1" and "Option Explicit" are at the top of the module (before any subs).

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Storm,


    Please do not quote an entire post when replying to a response, it just clutters up the thread.


    Quoting specific text is fine if it adds clarity to your question or response.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    KjBox, your solution works really well! Thank you very much for taking the time to provide me with this.


    Quote from KjBox

    Storm,
    Please do not quote an entire post when replying to a response, it just clutters up the thread.


    Noted! Next time I'll be more selective with quotes.

    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    KjBox,
    Sorry to mislead you. But I have found that the code you provided doesn't work as well as I thought it did.


    At first, when I put the value "P" in the cell to trigger the code it shows all of the sheets. After this the code breaks when it tries to hide the entire row in "Method Statements".


    Should this be pasted into the module for the Worksheet, or a separate module?

    It worked fine on the mock workbook I created which was based on a guess of what your actual workbook structure was like.


    The code should be in the worksheet object module.


    Can you copy your actual sheet which has the "P"s to a new workbook and attach that here. You can desensitize any confidential data but keep any formulas and formats. Also let me know how the sheet is used and what action you want to trigger the macro.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    KjBox,


    I have attached the sheet with the needed info. I think the issue is that the other code is conflicting with what you have written. Apologies for not making you aware of this before.


    There are a few ranges of cells that I have made act like check boxes (basically, on click insert "P"). I think your code is running whenever one of these cells is clicked when it should only be running for a certain range of these cells (the ones relating to the "Method Statements".

    Storm,
    I looked at KjBox code and your new spreadsheet. (I like your code Kj - learned something new about range passing)


    I found the following when debugging:
    sheet MS05... did not exist
    the index within wss() does not point to the correct sheet name.
    if all boxes are empty, (srow = "") it fails on the .range hide entire row command
    if all boxes are empty, the IIF command fails as shts array is empty


    I made some changes and included them in this attached spread sheet.
    the workbook now hides and unhides the rows and the sheets.


    KjBox, I would appreciate if you could look at the code and adjust it your way (so I can learn something new)
    Thanks Rabsofty

    Since you have the sheet names next to each "check box" it is possible to greatly simplify the code so that it runs for just the cell that has been checked or unchecked rather than looping through all the "check boxes".


    Note that I have made a new Named Range that includes all of the "check box" cells, this Named Range is named "Checks". I also removed the ":" from each sheet name in the cell next to each 'check box" so that it becomes an exact copy of a sheet name.


    The code also includes a check to ensure that each required sheet does actually exist (as rabsofty noted MS05... is missing in your sample file)


    The attached workbook has the new Named Range and new code, which is:

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Thank you both for you input, it's invaluable to me. Great to learn from both of your approaches too.


    KjBox, your solution of using the cell values to select the tabs makes so much sense. I will test this and get back to you.


    If I wanted to add a tab and "checkbox", how would I go about editing the code. I assume I would need to add something to the Target.Column Case statement?


    Please forgive my lack of knowledge of vba!

    It depends on where you add the new "Check box", if it is in column H, K M or P then nothing in the code needs to be changed. I you use a new column then, yes, you will need to add a new Case to the Select Case, the Case would be "Case Is = 20" if you use column T, then you would need to set the value for the variable i for that Case. The value of i is the number of cells to the left that of the check box cell that contains the Worksheet Name.


    In both cases you would need to add the new "check box" cell to the "Checks" Named Range.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    I found an error in the code. sheets 9 to 12 show not exist
    found error below


    in Case Is = 13, I should be 1


    Code
    If Not Intersect(Target, [Checks]) Is Nothing Then
        Select Case Target.Column
            Case Is = 8: i = 4
            Case Is = 11, 16: i = 2
            Case Is = 13: i = [COLOR=#0000FF]3[/COLOR]
        End Select

    thanks Kj for your code, I will be studying it closely.
    I am very well versed in VBA, however my knowledge of range.offset,intersect is limited.
    Your code will expand my knowledge.
    Thanks Rabsofty

    You're welcome, rab. I had to use the Select Case because of the merged cells being different for different columns (Storm, wherever possible avoid merged cells - there are a headache or disaster waiting to happen.


    Having said that, a far more efficient method would be to use either Form or ActiveX CheckBoxes, the Sheet Name could be the caption of each CheckBox, then I would use a Class Module Code to perform the same operation whenever a CheckBox gets checked or unchecked.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Here is a file using ActiveX CheckBoxes which have been assigned to a Class Module.


    There are 2 codes needed for this to work.


    In the Workbook Object Module


    And in a Class Module

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login