VBA Assistance - Hide / Unhiding a sheet based on cell contents

[V L]

Hello All: I have a workbook that contains multiple sheets . Certain sheets (not all) will have a value of 'closed' in C10. I would like to add some VBA that allows me to hide sheets that fit that criteria. Then, unhide them when necessary. Thank you for your assistance in advance...

 

[6StringJazzer]

We need a little more detail.

At what point do you want them evaluated for whether to hide them? Whenever the value in C10 changes to "closed"? Click a button? Open the file?

unhide them when necessary

When is it necessary? Are you expecting to unhide them manually or do you want code to do it? If you want code to do it, what event tells you to unhide them?

This will check all your sheets and hide the ones with "closed" in C10. But we need to wrap it in something that triggers it according to your answer to my first question.

   Dim WS As Worksheet
   
   For Each WS In Worksheets
   
      If WS.Visible = xlSheetVisible And UCase(WS.Range("C10")) = "CLOSED" Then
         WS.Visible = xlSheetHidden
      End If
   
   Next WS

 

[V L]

We need a little more detail.

At what point do you want them evaluated for whether to hide them? Whenever the value in C10 changes to "closed"? Click a button? Open the file?


When is it necessary? Are you expecting to unhide them manually or do you want code to do it? If you want code to do it, what event tells you to unhide them?

This will check all your sheets and hide the ones with "closed" in C10. But we need to wrap it in something that triggers it according to your answer to my first question.

   Dim WS As Worksheet
  
   For Each WS In Worksheets
  
      If WS.Visible = xlSheetVisible And UCase(WS.Range("C10")) = "CLOSED" Then
         WS.Visible = xlSheetHidden
      End If
  
   Next WS

TY for the assistance. I wanted to assign a button and run it as a macro...

 

[V L]

We need a little more detail.

At what point do you want them evaluated for whether to hide them? Whenever the value in C10 changes to "closed"? Click a button? Open the file?


When is it necessary? Are you expecting to unhide them manually or do you want code to do it? If you want code to do it, what event tells you to unhide them?

This will check all your sheets and hide the ones with "closed" in C10. But we need to wrap it in something that triggers it according to your answer to my first question.

   Dim WS As Worksheet
  
   For Each WS In Worksheets
  
      If WS.Visible = xlSheetVisible And UCase(WS.Range("C10")) = "CLOSED" Then
         WS.Visible = xlSheetHidden
      End If
  
   Next WS

TY for the assistance. I wanted to assign a button and run it as a macro...

 

[6StringJazzer]

You can take my code, wrap it in a sub, then assign it to a button.

 

[V L]

You can take my code, wrap it in a sub, then assign it to a button.

TY. That worked well.