Change event for all sheets in WBook
 

This works to hide/unhide for only the activesheet.

I need if I enter CS or SA on ANY sheet cell H8, then ALL sheets hide for that case, regardless what the other sheets display in cell H8.

A BOOLEAN ??? for all sheets to toggle from hide/unhide from the entry on any single sheet.

Thanks,
Howard


Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address < "$H$8" Then Exit Sub
If Target.Count 1 Then Exit Sub

Select Case Range("H8").Value

Case Is = "CS"
Rows("23:28").EntireRow.Hidden = True

Case Is = "SA"
Rows("23:28").EntireRow.Hidden = False

End Select

End Sub

Change event for all sheets in WBook
 

Hi Howard,

Am Thu, 2 Feb 2017 23:46:22 -0800 (PST) schrieb L. Howard:

This works to hide/unhide for only the activesheet.

I need if I enter CS or SA on ANY sheet cell H8, then ALL sheets hide for that case, regardless what the other sheets display in cell H8.

do you want to hide the rows if CS or SA is entered in H8? With any
other entry rows will unhide?
Then try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(0, 0) < "H8" Or Target.Count 1 Then Exit Sub

Dim flag As Boolean

Select Case Target.Value
Case "CS", "SA"
flag = True
Case Else
flag = False
End Select
For Each Sh In Worksheets
Rows("23:28").Hidden = flag
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Change event for all sheets in WBook
 

Hi again,

Am Fri, 3 Feb 2017 09:06:44 +0100 schrieb Claus Busch:

do you want to hide the rows if CS or SA is entered in H8? With any
other entry rows will unhide?
Then try:

sorry, I am wrong.

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address(0, 0) < "H8" Or Target.Count 1 Then Exit Sub

Dim flag As Boolean

Select Case Target.Value
Case "CS", "SA"
flag = True
Case Else
flag = False
End Select
For Each Sh In Worksheets
With Sh
.Rows("23:28").Hidden = flag
End With
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Change event for all sheets in WBook
 

On Friday, February 3, 2017 at 12:09:27 AM UTC-8, Claus Busch wrote:
Hi again,

Am Fri, 3 Feb 2017 09:06:44 +0100 schrieb Claus Busch:

do you want to hide the rows if CS or SA is entered in H8? With any
other entry rows will unhide?
Then try:

sorry, I am wrong.

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address(0, 0) < "H8" Or Target.Count 1 Then Exit Sub

Dim flag As Boolean

Select Case Target.Value
Case "CS", "SA"
flag = True
Case Else
flag = False
End Select
For Each Sh In Worksheets
With Sh
.Rows("23:28").Hidden = flag
End With
Next
End Sub


Hi Claus,

It hides with CS but does not unhide with SA.

It should hide all if I enter CS on sheet6 and unhide all if I enter SA on sheet2. As an example.

I have the code in the ThisWorkbook module.
Howard

Change event for all sheets in WBook
 

Hi Howard,

Am Fri, 3 Feb 2017 00:35:15 -0800 (PST) schrieb L. Howard:

It hides with CS but does not unhide with SA.

I thought it should hide with both values and unhide with any other.

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address(0, 0) < "H8" Or Target.Count 1 Then Exit Sub

Dim flag As Boolean

Select Case Target.Value
Case "CS"
flag = True
Case "SA"
flag = False
End Select
For Each Sh In Worksheets
With Sh
.Rows("23:28").Hidden = flag
End With
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Change event for all sheets in WBook
 

On Friday, February 3, 2017 at 12:48:25 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 3 Feb 2017 00:35:15 -0800 (PST) schrieb L. Howard:

It hides with CS but does not unhide with SA.

I thought it should hide with both values and unhide with any other.

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address(0, 0) < "H8" Or Target.Count 1 Then Exit Sub

Dim flag As Boolean

Select Case Target.Value
Case "CS"
flag = True
Case "SA"
flag = False
End Select
For Each Sh In Worksheets
With Sh
.Rows("23:28").Hidden = flag
End With
Next
End Sub

Hi Claus,

That works perfect! Many thanks

Howard