When I write text cell from one sheet, unhide row to another sheets together

[christopher81]

Helloooooo i need help

In sheet ,MENU i have 20 employees G2:G21

And another 12 sheets:
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
in cells A3:A22
are introduced automatically by the method ='MENU'!G2

The help you need is,
while lines 3:22 are hidden, when I write on the MENU G2 sheet any employee name, it automatically unhide on all twelve sheets with the months A3
and the same with the other cells
employee name G3, row unhide 4
employee name G4, row unhide 5
employee name G5, row unhide 6
..................
.................
.................

and hide rows to twelve sheets no text value MENU G2
G3
G4
......
.......

Any help appreciated.

 

[Joe4]

Welcome to the Board!

So, are the cells A3:A22 on the Menu sheet blank initially, and that is why you are hiding them?
And you want them unhidden on all the sheets once you unhide and populate one of these cells on the Menu sheet?
Is that how it is supposed to work?

 

[christopher81]

Welcome to the Board!

So, are the cells A3:A22 on the Menu sheet blank initially, and that is why you are hiding them?
And you want them unhidden on all the sheets once you unhide and populate one of these cells on the Menu sheet?
Is that how it is supposed to work?

Yes, exactly as you said

 

[Joe4]

Go to your MENU sheet, right-click on the Sheet Tab name that appears at the bottom of the screen, select "View Code", and paste the following VBA code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim ws As Worksheet
    Dim r As Long
   
'   See if any cells updated in G3:G22
    Set rng = Intersect(Target, Range("G3:G22"))
   
'   Exit if no cells updated in range
    If rng Is Nothing Then Exit Sub
   
'   Loop through updated cells
    For Each cell In rng
'       If if updated cell is not empty
        If cell.Value <> "" Then
'           Capture row number of cell
            r = cell.Row
'           Loop through all other sheets
            For Each ws In Worksheets
'               Skip menu sheet
                If ws.Name <> "MENU" Then
'                   Unhide row
                    ws.Rows(r).EntireRow.Hidden = False
                End If
            Next ws
        End If
    Next cell
       
End Sub

Now, as you populate values in G3:G22 on your Menu sheet, it will automatically unhide those same row numbers on all the other sheets.

 

[christopher81]

thank you for u time...

G3:G22 worked and unhide, but deleted value text no again hide
if change G2:G22 that I need and no worked

I've been looking for this method for 1 week, and from what I've seen it's relatively difficult.

 

[Joe4]

Sorry, I missed the second half of your request there. Just a few minor updates, nothing too hard.
Try this version, which will do BOTH things:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim ws As Worksheet
    Dim r As Long
    Dim h As Boolean
    
'   See if any cells updated in G3:G22
    Set rng = Intersect(Target, Range("G3:G22"))
    
'   Exit if no cells updated in range
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells
    For Each cell In rng
'       Determine whether or to hide depending on cell content
        h = (cell.Value = "")
'       Capture row number of cell
        r = cell.Row
'       Loop through all other sheets
        For Each ws In Worksheets
'           Skip menu sheet
            If ws.Name <> "MENU" Then
'               Hide/unhide row
                ws.Rows(r).EntireRow.Hidden = h
            End If
        Next ws
    Next cell
        
End Sub

 

[christopher81]

don't apologize, mistakes will always be made, and we learn from them ...

yes it finally works thanks to you, ??? and thanks to you others will use it if they need it.

A thank you I think is a little.

I understood why while I was asking for G2: G22 you had it G3: G22, because it should be in the same line, it doesn't matter, I changed the position of employees G 3: G 22 and all is well

that is, it does not matter in which column it will be, but it should be in the same line, otherwise I imagine it will be difficult;

thanks again. ?

 

[Joe4]

I understood why while I was asking for G2: G22 you had it G3: G22, because it should be in the same line, it doesn't matter, I changed the position of employees G 3: G 22 and all is well

that is, it does not matter in which column it will be, but it should be in the same line, otherwise I imagine it will be difficult;

Oh, I missed that too (I guess I was having a rough morning!)

That can actually be done with one simple adjustment.
Change this line:

                ws.Rows(r).EntireRow.Hidden = h

to this"

                ws.Rows(r+1).EntireRow.Hidden = h

 

[christopher81]

Oh, I missed that too (I guess I was having a rough morning!)

That can actually be done with one simple adjustment.
Change this line:

                ws.Rows(r).EntireRow.Hidden = h

to this"

                ws.Rows(r+1).EntireRow.Hidden = h

Is okkkkk thankyou ? ?

 

[Joe4]

You are welcome.
(Yes, I could have used a few more cups of that this morning!)