Hiding sheets based on a value in a cell

[JessicaKinnear]

Hey, I'm looking for some help on a VBA code.

I basically have a sheet named 'INPUTS-BULDING DETAILS' then I have 12 sheets named 'Tenant - 1', 'Tenant - 2' etc. all the way up to 12 which I want to hide if a certain cell doesn't contain a value.

On my inputs tab I want to be able to code so that if cell F36 has no value then hide sheet 'Tenant -1', if cell F37 has no value then hide sheet 'Tenant - 2', then this to repeat all the way up to cell F47 and sheet 'tenant - 12'

Does anyone know the correct code for this (I am a complete beginner when it comes to coding so think code for dummies )

Thank you in advance!

 

[JessicaKinnear]

it will be values

Shouldn't matter where the code is.
What is in F36:F47? is it had values or a formula?

 

[Fluff]

In that case make sure the the "blank" cells are totally empty.

 

[Marc L]

Jessica,​

​

did you ever try post #9 ?​

 

[JessicaKinnear]

thanks for your reply, this didn't seem to work either

Hi, according to your explanation a VBA demonstration to paste to the input worksheet module :​

Sub Demo1()
    Dim V, R%
        V = [IF({1},TRIM(F36:F47)>"")]
    For R = 1 To 12:  Sheets("Tenant - " & R).Visible = V(R, 1):  Next
End Sub

Jessica,​

​

did you ever try post #9 ?​

Hey, apologies i thought i replied. i did try it but this code also didn't work

 

[Marc L]

​

As both codes work on helpers side maybe it's time to link your workbook on a files host website like dropbox for example …​

 

[JessicaKinnear]

Ok, how about

Sub Jessica()
   Dim i As Long
  
   For i = 1 To 12
      Sheets("Tenant - " & i).Visible = Sheets("INPUTS-BULDING DETAILS").Range("F" & i + 35) <> ""
   Next i
End Sub

Hey, I've finally managed to get this to work. Thank you for your help. However my only issue now is that to get it to work I have to run the code. What I would like to happen is that if for example if I put a value in cell F36 I want it to hide the tenant 1 tab straight away instead of me having to press run code for it to happen. Is that possible?

 

[Fluff]

If you want it to be automatic, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F36:F47")) Is Nothing Then
      Sheets("Tenant - " & Target.Row - 35).Visible = Target.Value <> ""
   End If
End Sub

This needs to go in the INPUTS-BULDING DETAILS code module