Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109832 > unrolled thread

Change event for all sheets in WBook

Back to article view | Back to microsoft.public.excel.programming

Contents

  Change event for all sheets in WBook "L. Howard" <lhkittle@comcast.net> - 2017-02-02 23:46 -0800
    Re: Change event for all sheets in WBook Claus Busch <claus_busch@t-online.de> - 2017-02-03 09:06 +0100
      Re: Change event for all sheets in WBook Claus Busch <claus_busch@t-online.de> - 2017-02-03 09:09 +0100
        Re: Change event for all sheets in WBook "L. Howard" <lhkittle@comcast.net> - 2017-02-03 00:35 -0800
          Re: Change event for all sheets in WBook Claus Busch <claus_busch@t-online.de> - 2017-02-03 09:48 +0100
            Re: Change event for all sheets in WBook "L. Howard" <lhkittle@comcast.net> - 2017-02-03 01:02 -0800

#109832 — 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

[toc] | [next] | [standalone]

#109833

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

[toc] | [prev] | [next] | [standalone]

#109834

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

[toc] | [prev] | [next] | [standalone]

#109835

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

[toc] | [prev] | [next] | [standalone]

#109836

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

[toc] | [prev] | [next] | [standalone]

#109837

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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web