Hide sheet named to a cell on a front sheet

[Markth]

Hi,

i need to hide worksheets based on the content of a cell. I have a summary sheet (called "voorblad") the trigger for one sheet should be the contect of a cell in collumn Q (i.e. Q7) and the name of the sheet is one the same row in collumn C (i.e. C7).
this repeats every 3 rows.

I want to check if "Q7" is 0 then the sheet with name as in cell C7 should hide. Then repeat this for every third row after row 7. (row 10, 13, 16.....)

I have now this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [Q7] <> 0 Then
Sheets("7").Visible = True
Else
Sheets("7").Visible = False
End If
End Sub

This works but now I need to repeat it and get the sheets("7") replace by the name as in cell B7.

Any help is very welcome.

rgds,
mark

 

[Markth]

It absolutely HAS to be in the Sheet module for the sheet that you want to run it against. If you put it in a General Module, it will NOT run.

Event Procedure VBA code is VBA code that automatically runs upon some event happening. It ONLY runs if placed in the proper Module (one of the ThisWorkbook or Sheet modules), and has to be named a certain way (changing the name will make it not run). So there are some pretty strict requirements there that must be followed in order for it to work. And Macros/VBA must be enabled, and events must not be disabled in order for it to run.


It is also important to understand how the different Event Procedures run. A Worksheet_Change event procedure only runs if the value in a cell is manually updated. It does NOT run if a value is changed by a formula.
There is a Worksheet_Calculate event procedure that runs when a formula is calculated/re-calculated, but you really cannot tie that down to specific cells.

If your formula in column Q is the sum of cells C through P in the same row, then change the macro to run when a cell in columns C through P is changed, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    Dim shtName As String

'   Exit procedure if update not in range C7:P196
    Set myRange = Intersect(Target, Range("C7:P196"))
    If myRange Is Nothing Then Exit Sub

'   Loop through each cell in changed range
    For Each cell In myRange
'       Only proceed if remainder of row divided by 3 is one
'       (so we are only looking at rows 7, 10, 13, 16, ...)
        If (cell.Row Mod 3 = 1) Then
'           Capture name of sheet from column B
            shtName = Range("B" & cell.Row)
            On Error GoTo err_check
'           Check value in column Q, and hide/unhide sheet
            If Range("Q" & cell.Row) <> 0 Then
                Sheets(shtName).Visible = True
            Else
                Sheets(shtName).Visible = False
            End If
            On Error GoTo 0
        End If
    Next cell
    
    Exit Sub
    
'   Error coding if sheet name not found
err_check:
    If Err.Number = 9 Then
        MsgBox "No sheet found with name " & shtName, vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR!"
    End If
            
End Sub

Hi Joe,

Thanks a lot! now it works like a charm! It's those small things you just need to know. Thanks again for the code!

regards,
Mark

 

[Joe4]

You are welcome!
Yes, the devil is usually in the details.
A good rule of thumb is provide as much information as you can, as sometimes things which you don't think will affect anything are sometimes important.