PDA

View Full Version : VBA Help using value in sheet 1 to hide unhide row in sheet 2



hwbell3
05-05-2020, 10:05 AM
I have created the following VBA code that works well to hide and unhide rows in a given sheet. I use this basic code in a number of sheets where the "University-wide" and "College by College" Cases are used and selected from a drop box. My challenge is I want to be able to set the value of "L8" in sheet 1 and use it in all of the other sheets where this code appears instead of having to select the value in each sheet where the code is used.

How do I do this?



Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("L8"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "University-wide": Range("48:216").EntireRow.Hidden = True
Range("10:47,217:218").EntireRow.Hidden = False
Case Is = "College by College": Range("10:30,47:220").EntireRow.Hidden = False
Range("31:46").EntireRow.Hidden = True
End Select
End If
End Sub

Paul_Hossler
05-05-2020, 12:44 PM
Not tested, but the For Each ws ..... loop goes through all the worksheets




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet

If Application.Intersect(Range("L8"), Target) Is Nothing Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets

Select Case Target.Value
Case Is = "University-wide"
ws.Rows("48:216").Hidden = True
ws.Rows("10:47,217:218").Hidden = False
Case Is = "College by College"
ws.Rows("10:30,47:220").Hidden = False
ws.Rows("31:46").Hidden = True
End Select

Next
End Sub

hwbell3
05-06-2020, 10:25 AM
Paul,

Thanks for the Code. Initially I omitted the "Option Explicit" and got error messages. When I added the "Option Explicit" the error messages disappeared but the code did not work. Any thoughts on what might be keeping the code from working?

hwbell3

paulked
05-06-2020, 01:29 PM
Do you want to run this on every sheet in the workbook or just a selection? If a selection, which sheets? Or post your workbook (remove sensitive data if you need to).

hwbell3
05-06-2020, 02:19 PM
I want to run it on a selection of sheets. More specifically, I want to use the "University-wide" and "College by College" values to hide and unhide rows or columns on Sheets 2, 3, 6, 8, 12, 13, 26, 27, 29, 40, and 41. Part of the challenge is that the rows and columns hidden and unhidden vary from sheet to sheet. Hence, I need to be able to use the "University-wide" and "College by College" values on each of the identified sheets to hide a unique set of rows or columns on each of the identified sheets. I am, therefore, using a different code for each worksheet. Currently I have written a custom code for each sheet that works for each sheet. The code I showed as an example was the code for sheet6. But I don't want to force a user to set the value of "University-wide" or "College by College" 11 different times. Hence, my challenge is to be able to have a user set the value as either "University-wide" or "College by College" in one of the worksheets and have that value activate the code for each of the other sheets where it is used.

paulked
05-06-2020, 02:50 PM
In that case I would suggest that if cell L8 changes on any of those sheets, update cell L8 on all the ones that you want to update. Then in each sheet code module show/hide the rows you want on a Sheet_Activate event. When the sheet is selected the rows will be updated. Example:



Option Explicit

Private Sub Worksheet_Activate()
Select Case Range("L8")
Case Is = "University-wide"
Range("48:216").EntireRow.Hidden = True
Range("10:47,217:218").EntireRow.Hidden = False
Case Is = "College by College"
Range("10:30,47:220").EntireRow.Hidden = False
Range("31:46").EntireRow.Hidden = True
End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, str As String
If Application.Intersect(Range("L8"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
str = Range("L8")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet2" Or ws.Name = "Sheet3" Or ws.Name = "Sheet6" Or _
ws.Name = "Sheet8" Or ws.Name = "Sheet12" Or ws.Name = "Sheet13" Or _
ws.Name = "Sheet26" Or ws.Name = "Sheet27" Or ws.Name = "Sheet29" Or _
ws.Name = "Sheet29" Or ws.Name = "Sheet40" Or ws.Name = "Sheet41" Then
ws.Range("L8") = str
End If
Next
Application.EnableEvents = True
End Sub

Paul_Hossler
05-06-2020, 04:07 PM
The code I showed as an example was the code for sheet6

Q1. So this



If Not Application.Intersect(Range("L8"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "University-wide": Range("48:216").EntireRow.Hidden = True
Range("10:47,217:218").EntireRow.Hidden = False
Case Is = "College by College": Range("10:30,47:220").EntireRow.Hidden = False
Range("31:46").EntireRow.Hidden = True
End Select End If


was the rows to hide/unhide on Sheet6, but Sheet 2 has a different group of rows?

Q2. Does each sheet key off of the L8 on THAT sheet? I.e. Sheet2 L8 = University-wide and hides/unhides it's set of rows, and on Sheet6 L8 = College by College which hides that sheet's rows?

Q3. Have you thought about naming the worksheets something more meaningful that "Sheet2", say "North East Schools"?

Q4. Is there a 'signature' on the affected sheets that indicates that the sheets is 'on the list'? Like maybe if B2 has "Courses" then use the L8 logic, otherwise don't on that sheet?

Q5. Similarly, are there markers that could be used to determine dynamically the show/hide ranges?

Q6. Last one --- can you attach a sample workbook?


Maybe in the ThisWorkbook module



Option Explicit


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


If Intersect(Target, Sh.Range("L8")) Is Nothing Then Exit Sub

If Sh.Range("L8").Value <> "University-wide" And Sh.Range("L8").Value <> "College by College" Then Exit Sub

Application.EnableEvents = False

'only sheet 6 is really correct (probably). Rest need to be made specific


Call ShowHide("Sheet2", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet3", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet6", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet8", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet12", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet13", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet26", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet27", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet29", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet40", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet41", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")


Application.EnableEvents = True


End Sub





Private Sub ShowHide(ws As String, UorC As String, Uhide As String, Ushow As String, Chide As String, Cshow As String)

With Worksheets(ws)
.Range("L8").Value = UorC

Select Case UorC
Case Is = "University-wide"
.Rows(Uhide).Hidden = True
.Rows(Ushow).Hidden = False
Case Is = "College by College"
.Rows(Cshow).Hidden = False
.Range(Chide).Hidden = True
End Select
End With


End Sub

Paul_Hossler
05-06-2020, 04:35 PM
Paul,

Thanks for the Code. Initially I omitted the "Option Explicit" and got error messages. When I added the "Option Explicit" the error messages disappeared but the code did not work. Any thoughts on what might be keeping the code from working?

hwbell3


Option Explicit says that all variables have to be explicitly Dim-ed

I don't see how omitting that would cause errors, that went away when you added it

"Any thoughts" ... No, not without my crystal ball

hwbell3
05-07-2020, 10:41 AM
Paulked,

I tried the code you suggested. When I ran it I got not error messages but I also did not get it to work.

Hwbell3

paulked
05-07-2020, 11:03 AM
Post your workbook or try Paul H's latest code