Hi guys,
I need some help. I have a data entry tab with 184 row chart of accounts. That entry tab is linked to 68 tabs other tabs in the worksheet which are used for different purposes, calculating different items on the chart of the account on each tab, but they all use the same set of rows. So, I have a helper cell on each of the 68 tabs that says "Yes" when a unused row in the chart of accounts has a value of 0.
Then, I have a toggle button on the data entry tab that says with "Hide Rows" and "Unhide Rows". When its pressed, it Hides and Unhides the rows on the chart of accounts that are a value of 0 on all 68 tabs. When I press Unhide, it Unhides all of the rows with the value 0. My issue is that when I enter a value on the data entry tab for a row that is already hidden on the other 68 tabs already, when I press Unhide, the row remains hidden for some reason and I don't know why.
My thought was to remove the if statement criteria of "Yes" and just have all rows on the 68 tabs Unhide regardless of value, yes etc. but when I tried to modify the if statement, I am getting a "runtime error '1004' - unable to set Hidden property of the Range class" error.
Hope someone has some ideas to get this figured out. I'm still learning VB so I am at a loss on how to fix it. Thanks in advance for your help!
The toggle button code is as follows:
Module9 code is as follows:
I need some help. I have a data entry tab with 184 row chart of accounts. That entry tab is linked to 68 tabs other tabs in the worksheet which are used for different purposes, calculating different items on the chart of the account on each tab, but they all use the same set of rows. So, I have a helper cell on each of the 68 tabs that says "Yes" when a unused row in the chart of accounts has a value of 0.
Then, I have a toggle button on the data entry tab that says with "Hide Rows" and "Unhide Rows". When its pressed, it Hides and Unhides the rows on the chart of accounts that are a value of 0 on all 68 tabs. When I press Unhide, it Unhides all of the rows with the value 0. My issue is that when I enter a value on the data entry tab for a row that is already hidden on the other 68 tabs already, when I press Unhide, the row remains hidden for some reason and I don't know why.
My thought was to remove the if statement criteria of "Yes" and just have all rows on the 68 tabs Unhide regardless of value, yes etc. but when I tried to modify the if statement, I am getting a "runtime error '1004' - unable to set Hidden property of the Range class" error.
Hope someone has some ideas to get this figured out. I'm still learning VB so I am at a loss on how to fix it. Thanks in advance for your help!
The toggle button code is as follows:
Code:
Private Sub ToggleButton9_Click()
If ToggleButton9.Value Then
Call Module9.Hide_Rows_Containing_Value_All_Sheets
ToggleButton9.Caption = "Unhide Rows"
Else
ToggleButton9.Caption = "Hide Rows"
Call Module9.Unhide_All_Rows
End If
End SubModule9 code is as follows:
Code:
Sub Hide_Rows_Containing_Value_All_Sheets()
Dim c As Range
Dim ws As Worksheet
Dim cR As Range
Set cR = Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook.Worksheets
With ws
Set cR = .Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")
For Each c In cR
If c.Value = "Yes" Then c.EntireRow.Hidden = True
Next c
End With
Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Unhide_All_Rows()
Dim c As Range
Dim ws As Worksheet
Dim cR As Range
Set cR = Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook.Worksheets
With ws
Set cR = .Range("DF8:DF12,DF19:DF28,DF35:DF209,DF244:DF252,DF259,DF261")
For Each c In cR
If c.Value = "Yes" Then c.EntireRow.Hidden = False
Next c
End With
Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
