Solved: Hide and shove worksheet depending on if something is true

[Rejje]

Problem: I need a worksheet to be shown only if something is true; else hide worksheet.

How is it done?

[Chabu]

use

Dim sheet As Worksheet
Set sheet = Worksheets("Sheet1")
If (some condition) Then
sheet.Visible = True
Else
sheet.Visible = False
End If

[Rejje]

I tried:

Private Sub ShowSJR()
Dim sheet As Worksheet
Set sheet = Worksheets("SJR")
If Range("V_20100") = True Then
sheet.Visible = True
Else
sheet.Visible = False
End If
End Sub

I placed code in ThisWorkbook. It still won't hide worksheet "SJR" when Range("V_20100") = False
What is wrong?

[Paul_Hossler]

I think you'll need to put the code into the ThisWorkbook events


Option Explicit

Private Sub Workbook_Open()
Call Check_Range
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call Check_Range
End Sub

Private Sub Check_Range()
If Range("V_20100") = True Then
Worksheets("SJR").Visible = xlSheetVisible
Else
Worksheets("SJR").Visible = xlSheetHidden
End If
End Sub


I used a second sheet to manually control the V_20100 T/F value

Paul

[Chabu]

Is this range defined on the SJR sheet itself? If yes then you need to qualify the Range("V_20100") by writing sheet.Range("V_20100")

Put your code in a Module if it is to be triggerred by another piece of code or do as Paul said above.

[Rejje]

I think you'll need to put the code into the ThisWorkbook events


Option Explicit

Private Sub Workbook_Open()
Call Check_Range
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call Check_Range
End Sub

Private Sub Check_Range()
If Range("V_20100") = True Then
Worksheets("SJR").Visible = xlSheetVisible
Else
Worksheets("SJR").Visible = xlSheetHidden
End If
End Sub


I used a second sheet to manually control the V_20100 T/F value

Paul

Works perfect! Many, many thanks!

[Rejje]

Is this range defined on the SJR sheet itself? If yes then you need to qualify the Range("V_20100") by writing sheet.Range("V_20100")

Put your code in a Module if it is to be triggerred by another piece of code or do as Paul said above.

No, Range("V_20100") is in another worksheet ("V") that I use for all the scripts to check in for logics/true/false and such (sort of a "to-do-list"). Yet: ranges in this worksheet (mostly single cells) are all defined for the whole workbook so that should never be a problem (I think at least).