I am trying to create a workbook which hides inactive sheets based on a cell value on the front sheet. i.e if the cell value us "yes" it is visible, if it is "no" it is hidden.
Private Sub Worksheet_Change(ByVal Target As Range) If [A9] = "Yes" Then Sheets("VAR 001").Visible = True Else Sheets("VAR 001").Visible = False End If End Sub
I need this to happen for multiple sheets. I have used this macro which works on a single sheet but I would like the other sheets named VAR 002 up to VAR 250 to operate the same way but with each sheet referencing a different cell for Yes/No.
VAR 002 need to reference cell A10, VAR 003 needs to reference cell A11 and so on up to VAR250 referencing call A258.
@AB_2019 If the sheet names are tied to the row numbers, you can use the code below. It will only run when a cell in the range A9 to A258 is changed. It also works if you copy and paste "Yes" or another value into more than one cell at a time. (Note that this is case sensitive, so "yes" will hide the sheet and only "Yes" will show it)
Let me know how you get on with this.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Integer
Dim mySheetName As String
Dim cel As Range
' watch only cells A9 to A258
If Not Intersect(Target, Range("A9:A258")) Is Nothing Then
' if more than one cell has been changed, we need to cath them all
For Each cel In Target
' get the row of the changed cell
' subtract 8 to arrive at the sheet number
myRow = cel.Row - 8
' build the sheet name
mySheetName = "VAR " & Format(myRow, "000")
If cel = "Yes" Then
ThisWorkbook.Worksheets(mySheetName).Visible = True
ThisWorkbook.Worksheets(mySheetName).Visible = False
Best Response confirmed by
AB_2019 (Occasional Contributor)