Recalculate to hide rows

[Guest]

My code to hide a row works when I am on that sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Contents").Range("VATYN") = "Y" Then
ActiveSheet.Rows("8:8").Hidden = False
Else
ActiveSheet.Rows("8:8").Hidden = True
End If
End Sub

this code sits on several sheets (not all)
How do I force a re-calc on the sheets without physically opening them one
at at time?

 

[Bob Phillips]

Sorry, what do you think that a re-calc on the sheets will achieve? The code
you show is triggered by a cell selection, any cell, which is wasteful as
you only need to do it when VATYN changes.

Also, is VATYN on every sheet, is Contents the worksheet that the proc is
in, or a generic checksheet/value?


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob
I want to hide row 8 on several sheets if VATYN = N (& unhide if VATYN=Y) &
I inserted the code into the relevant sheets, the only way I can find to make
the code work is to run calc - how do I hide rows on my selection if VATYN
(only on the Contents sheet) is N

 

[Guest]

go to the ThisWorkbook module for your project in the vBE. In that module,
select this event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

End Sub

add your code:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Static YesOrNo as String
Dim sh as Worksheet
if YesOrNo <> Sheets("Contents").Range("VATYN").Value then
for each sh in worksheets(Array("Sheet1","Sheet5","Sheet8"))
If Sheets("Contents").Range("VATYN") = "Y" Then
sh.Rows("8:8").Hidden = False
Else
sh.Rows("8:8").Hidden = True
end if
Next
end if
YesOrNo = Sheets("Contents").Range("VATYN").Value
End Sub

Alter the array to list the sheets where you want this to occur

Remove your current code that does this in the sheet module of those
sheets.

 

[Guest]

Tom
Thanks very much for this!
My problem is that the array sheets are not created up front. My idea was to
have a 'template' worksheet, which when copied would pick up the hide rows
code automatically. Is this possible?
ie if thissheet!A1=Y then hide rows; else unhide
I could link A1 to my contents page VATYN

Thanks for you perserverance

Saintsman