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

 

[Joe4]

Welcome to the Board!

Note that you have placed the code in a Worksheet_Change event. That only runs when your specified cells are updated. Is that what you want to happen?
So, usually it is only running on one cell at a time.
How are these values in column Q being updated?
You mentioned every third row, how far down does it go?

 

[Markth]

Welcome to the Board!

Note that you have placed the code in a Worksheet_Change event. That only runs when your specified cells are updated. Is that what you want to happen?
So, usually it is only running on one cell at a time.
How are these values in column Q being updated?
You mentioned every third row, how far down does it go?

Yep that's correct no problem the first sheet will filled out and trigger the hide/show.
the cells in collum Q are updated manually.
it goes down to row 196.

 

[Joe4]

From your first post, I am unsure if you sheet name is in column B or C (you mention both). Here is the code if it is in column C. Adjust as needed:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range

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

'   Loop through each cell in changed range
    For Each cell In myRange
        If cell <> 0 Then
            Sheets(Cells(cell.Row, "C")).Visible = True
        Else
            Sheets(Cells(cell.Row, "C")).Visible = False
        End If
    Next cell
    
End Sub

 

[Markth]

From your first post, I am unsure if you sheet name is in column B or C (you mention both). Here is the code if it is in column C. Adjust as needed:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range

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

'   Loop through each cell in changed range
    For Each cell In myRange
        If cell <> 0 Then
            Sheets(Cells(cell.Row, "C")).Visible = True
        Else
            Sheets(Cells(cell.Row, "C")).Visible = False
        End If
    Next cell
    
End Sub

Thanks Joe,

It only does not do nothing. Is it possible to have the loop jump 3 rows? Let see row 7 is the first then row 10, then row 13....?
The names are in collum B. I changed that in the code, but no Sheets are hidden.

regards,
Mark

 

[Joe4]

Try this variation:

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 Q7:Q196
    Set myRange = Intersect(Target, Range("Q7:Q196"))
    If myRange Is Nothing Then Exit Sub

'   Loop through each cell in changed range
    For Each cell In myRange
        If (cell.Row Mod 3 = 1) Then
            shtName = Range("B" & cell.Row)
            On Error GoTo err_check
            If cell <> 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

 

[Markth]

Try this variation:

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 Q7:Q196
    Set myRange = Intersect(Target, Range("Q7:Q196"))
    If myRange Is Nothing Then Exit Sub

'   Loop through each cell in changed range
    For Each cell In myRange
        If (cell.Row Mod 3 = 1) Then
            shtName = Range("B" & cell.Row)
            On Error GoTo err_check
            If cell <> 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

Sorry to say, but it does not do nothing. Don't know the problem. looks like it just exits the sub? but it does not hide anything.

thanks for your efforts.

Mark

 

[Joe4]

I tested it out, and it works for me. So let's see if we can find out where the difference is.

First off, where have you placed this VBA code? Is it in the Sheet module of the sheet you want to run it against? If it is in a general module, it will now work.

If you are confident that is correct, then lay out a scenario for me, letting me know what value you are changing in what cell, and what is in column B of that row.

 

[Markth]

I tested it out, and it works for me. So let's see if we can find out where the difference is.

First off, where have you placed this VBA code? Is it in the Sheet module of the sheet you want to run it against? If it is in a general module, it will now work.

If you are confident that is correct, then lay out a scenario for me, letting me know what value you are changing in what cell, and what is in column B of that row.

Hi Joe,

Hi had it in a worksheet module but put it in to a general module, still did not do anything.

Here is what happens:
It's about timesheets. The front page is a summary sheet in which I have 2 collumns per day with a name collumn [A] and a person number collumn in front of them. Next to that I have a timesheet-worksheet per person and the name of those worksheets is the personnumber. In the summary sheet I have persons that are no longer working on the project and I want to hide their timesheet - worksheet.
In collumn Q I have a "Sum" function that sums the hours that are manually changed in collumns C-P. So in case there's no hours filled out there Q is 0 and the timesheet- sheet can be hidden.

I hope it's clear.

regards,
Mark

 

[Joe4]

Hi had it in a worksheet module but put it in to a general module, still did not do anything.

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.

In collumn Q I have a "Sum" function that sums the hours that are manually changed in collumns C-P. So in case there's no hours filled out there Q is 0 and the timesheet- sheet can be hidden.

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