Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get values from 15 sheets in a Wbook Carpe Diem Excel Programming 0 August 6th 08 11:15 AM
Copy cell (C6) from multiple sheets in a wbook to another workbook [email protected] Excel Programming 2 February 9th 07 11:28 PM
Copy cell (C6) from multiple sheets in a wbook to another workbook [email protected] Excel Programming 0 February 9th 07 04:30 PM
Copy cell (C6) from multiple sheets in a wbook to another workbook [email protected] Excel Programming 0 February 9th 07 04:30 PM
Copy cell (C6) from multiple sheets in a wbook to another workbook [email protected] Excel Programming 0 February 9th 07 04:30 PM


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"