Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate to hide rows
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate to hide rows
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) "Saintsman" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate to hide rows
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 "Bob Phillips" wrote: 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) "Saintsman" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate to hide rows
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. -- Regards, Tom Ogilvy "Saintsman" wrote: 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 "Bob Phillips" wrote: 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) "Saintsman" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculate to hide rows
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Saintsman" wrote: 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 "Bob Phillips" wrote: 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) "Saintsman" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group rows (or hide rows) like in MS Project | Excel Worksheet Functions | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Specify which rows to NOT hide, and have excel hide the rest | Excel Programming | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions |