VBA to Hide/unhide Rows & Columns on multiple sheets within a workbook based on activation cells

[Adam1988]

Hi all,

I have workbook that I would like to automatically hide/unhide rows & columns based on values.

I have 3 activation cells on a worksheet called "Capacity Requirements".

• D7: The value of that cell can be 1-7 and is intended to determine the term of the model (from 2016-2022). In row 2, I have formulas in cells E2:R2 that will either return a 1 or 0 based on if that year should be included in the mode (there are two columns for each year - 2016 is in columns E & F). If the value 0 is returned, then the column should be hidden and if the value 1 is returned the column should be unhidden.
• C34: This cell can either be blank or have some text in it. If it is blank my formula in cells A35:A38 will return 0 (these rows should be hidden), if there is text in C34 the formulas will return 1 (row 35:38 should be unhidden)
• C39: This cell can either be blank or have some text in it. If it is blank my formula in cells A40:A43 will return 0 (these rows should be hidden), if there is text in C39 the formulas will return 1 (row 40:43 should be unhidden)
• C44: This cell can either be blank or have some text in it. If it is blank my formula in cells A45:A48 will return 0 (these rows should be hidden), if there is text in C44 the formulas will return 1 (row 45:48 should be unhidden)

I have been using the code below within the worksheet and it works great.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


Private Sub Worksheet_Change(ByVal Target As Excel.Range)


If Target.Address = "$D$7" Then
    Application.ScreenUpdating = False
    For i = 5 To 18
    If Cells(2, i) = 0 Then
    Columns(i).EntireColumn.Hidden = True
    Else
    Columns(i).EntireColumn.Hidden = False
    End If
    Next i
    Application.ScreenUpdating = True
End If


If Target.Address = "$C$34" Then
    Application.ScreenUpdating = False
    For j = 35 To 38
    If Cells(j, 1) = 0 Then
    Rows(j).EntireRow.Hidden = True
    Else
    Rows(j).EntireRow.Hidden = False
    End If
    Next j
    Application.ScreenUpdating = True
End If


If Target.Address = "$C$39" Then
    Application.ScreenUpdating = False
    For k = 40 To 43
    If Cells(k, 1) = 0 Then
    Rows(k).EntireRow.Hidden = True
    Else
    Rows(k).EntireRow.Hidden = False
    End If
    Next k
    Application.ScreenUpdating = True
End If


If Target.Address = "$C$44" Then
    Application.ScreenUpdating = False
    For l = 45 To 48
    If Cells(l, 1) = 0 Then
    Rows(l).EntireRow.Hidden = True
    Else
    Rows(l).EntireRow.Hidden = False
    End If
    Next l
    Application.ScreenUpdating = True
End If


End Sub

As my workbook has grown over time, my model requires more worksheets. I would now like my activation cells (D7, C34, C39, and C44) on the "Capacity Requirements" worksheet to automatically hide/unhide rows and columns on other worksheets through the workbook.

For example, when the model term is set to 6, I would like to hide any columns that refer to 2022. On my "Results" worksheet, I have formulas that return 1 or 0 in cells K2:Q2 (1 column per year). When 6 is selected in cell D7 on the "Capacity Requirements" worksheet column Q on the "Results" worksheet should automatically be hidden and Columns K, L, M, N and O should be unhidden if they are currently hidden.

I have a number of other rows/columns in different worksheets that should also be hidden/unhidden based on the values in cells D7, C34, C39, and C44 on the "Capacity Requirements" worksheet.

Any help on this is greatly appreciated.

Thanks,
Adam1988

 

[JLGWhiz]

You could probably just change the macro title to:

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

and move the macro to the ThisWorkbook code module. That would satisfy the multiple sheet problem, if I understand the post correctly.

And this could probably be handled with an If...Then statement, but it needs to be explained a little clearer before I attempt to write code for it.
It is confusing to me about which sheet in the narrative below is going to activate the macro under which condition.

For example, when the model term is set to 6, I would like to hide any columns that refer to 2022. On my "Results" worksheet, I have formulas that return 1 or 0 in cells K2:Q2 (1 column per year). When 6 is selected in cell D7 on the "Capacity Requirements" worksheet column Q on the "Results" worksheet should automatically be hidden and Columns K, L, M, N and O should be unhidden if they are currently hidden.

 

[Adam1988]

And this could probably be handled with an If...Then statement, but it needs to be explained a little clearer before I attempt to write code for it.
It is confusing to me about which sheet in the narrative below is going to activate the macro under which condition.

Hi JLGWhiz,

Thank you for your response.

To your question, above...

Activation cell is D7 on the "Capacity Requirements" worksheet. Value 1-7 can be selected to determine the term of the model. If 1 is selected only year 2016 should be shown, if 7 then years 2016-2022 should be shown.

• On the "Capacity Requirement" worksheet I have a table of data with 2 columns for each year. Table range is E13:R54. In that table columns E & F show data for 2016, G & H show data for 2017, etc.
• On the "Results" worksheet, I have a similar table that shows the results of some calculations for each year. This table is in cells K24:Q28. In the table, column K is for 2016, L is for 2017, etc.
• When D7 on the "Capacity Requirements" worksheet is set to 4, I want 2016, 2017, 2018, and 2019 to be unhidden in both tables on both worksheets and I want 2020, 2021 and 2022 to be hidden on both worksheets.
• For the "Capacity Requirements" worksheet columns E:L should be unhidden and columns M:R should be hidden.
• For the "Results" worksheet, columns K:N should be unhidden and columns O:Q should be hidden

Does that help explain what I am trying to accomplish?

Thanks again,
Adam1988

 

[JLGWhiz]

When you start changing parameters withou any explanation for the change, it does nothing but confuse people who cannot see you worksheets and have no priorknowledge of your objectives. In post #1 you were using Model term = 6 and a different set of rows and columns, now it is a 4. I think I will just let someone else attempt to respond to this.
Regards, JLG