Hiding multiple sheets based on true/false status of cells

  • Hi all,


    New (and exasperated) VBA user here. I have browsed the forum trying to cannibalize VBA code to achieve my goal, but I think I will finally have to admit defeat.


    I am trying to hide subsequent named sheets based on the true/false status on sheet 1 for various products (for example, cell D9 with a "FALSE" status would hide from sheet "ZAPEX ZW KK ZWN" to sheet "ZAPEX ZW KK ZWS_ZBR_ZWB_ZWBT MP",) but to be quite frank, I am struggling to get it to hide even just 1 sheet. The code I originally tried was as follows:



    Now I admit I know I have done something wrong (as it doesn't work), but for the life of me I am not sure what it is!


    Could someone please just provide me with some pointers so I can start pushing ahead?


    Many thanks in advance for any guidance!


    Regards
    Mat

  • Should rngSheets be referencing column A rather than D as D contains True/False, and then it should be offsetting by 3?


    That said, your values in A do not appear to match sheet names, so I am slightly confused.

  • Try this but not test

  • So does the

    Code
    Set rngSheets = .Range("D9", .Range("D9").End(xlDown))

    define where the VBA code starts in reference point for the True/False statement, then the

    Code
    ThisWorkbook.Worksheets(rngSht.Value).Visible = (rngSht.Offset(0, 1).Value = "TRUE")

    offsets the cell it looks from? I ideally need the code to identify if the cell states True or False, then hide a batch of worksheets if the status is False. Kind Regards Mat

  • But in your file, D9 contains "TRUE" (your data actually starts in row 5 so not sure why your code starts at 9) so then this line

    Code
    ThisWorkbook.Worksheets(rngSht.Value).Visible = (rngSht.Offset(0, 1).Value = "TRUE")


    is looking for Worksheets("TRUE") which doesn't exist. The offset is column E which is empty.

  • Hi Stephen,


    Oh so the code is looking for a worksheet named "TRUE", not a cell? That would explain it then!


    Regarding starting in D9, that is because that TRUE/FALSE statement is related to the first batch of worksheets (convenience just to check my coding).


    Regards
    Mat

  • Well it is looking for a cell and then it is taking the value of that cell which is "TRUE". In the loop this line is saying

    Code
    ThisWorkbook.Worksheets(rngSht.Value).Visible = (rngSht.Offset(0, 1).Value = "TRUE")


    look for the sheet named the same as the value of the cell rngSht, which is D9 in the first iteration. That's why I suggested you use column A.

  • So, I have been doing some more tinkering, trying different codes (without really knowing what I am doing), and now I have got it to try and process something, but nothing changes.


    The code I have is a slight variation on the one provided by graha_kayra above, but as follows:



    I have tried to define just one sheet for it to change, and limited the range to just that specific cell....the macro seems to run, but does not change anything.


    I have attached a screen shot of the cell that I am testing against, and the tab which should hide when the status is false...but as you can probably guess, zero success at the moment! (I am trying to teach myself all I can via various websites, and so far I have learnt how to change the colour of a box....)


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.JPG","data-attachmentid":1210452}[/ATTACH]


    Many thanks in advance for any help
    Mat

Participate now!

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