VBA: Activate macro on multiple other worksheets using a button

[Pascawali]

Afternoon all

First and foremost, thank you for your help in advance. Greatly appreciate it.

I am building a workbook whereby I have a code in 4 worksheets that hides columns based on a value found in row 5.
The code in each of these worksheets is within their own sub and is as follows:

Sub Hide_Columns_Containing_Value()    
Dim c As Range


    For Each c In Range("B5:AS5").Cells
        If c.Value = "xxx" Then
            c.EntireColumn.Hidden = True
        Else: c.EntireColumn.Hidden = False


           
        End If
    Next c


End Sub

The results in Row 5 are all dependant on the answers provided in an alternative worksheet "Chart of Accounts".
I want to include a button that will refresh and run the macro on each of the 4 worksheets, so when any changes are made in "Chart of Accounts", the user can refresh each of the 4 worksheets.
Research suggests I need to use a 'module' however I got lost at this point.

If anybody could give assistance on the required code for this macro, that would be appreciated.

Thanks!

 

[My Aswer Is This]

We may not need any button to do what you want.

What happens on sheet "

Chart of Accounts"

That causes the range on another sheet to equal "xxx"

We could tell the script to run on all the sheets when this condition is met on sheet named "Chart of accounts"


Or we could just have a Button on sheets "Chart of accounts" which when pressed would hide columns B to AS on all the sheets.

We would need to know what sheets to apply this script to.

Like sheets named "Alpha" "Bravo" "Delta" and "Echo"

​

 

[Pascawali]

Hey mate

Thanks for your response - yeah I considered not having a button, but the VBA code was WAY too slow.
This was because every time I clicked on the worksheet, it was searching for a change in the "Chart of Accounts" Worksheet, causing significant lag.

The change in the chart of accounts is a "yes/no" list. These yes/no responses are transposed to row 5 in the other worksheets.
If the answer to a row is "No" in "Chart of Accounts', then I want the macro to hide the columns in the other worksheets that show "No" in row 5.
Basically, if the answer is "No" then the column should not be active and thus hidden.
There are 26 different variables, all with potential "Yes/No" that are transposed through to the 4 worksheets.

The other worksheets titles are:
Q1 - Income & Expenses
Q2 - Income & Expenses
Q3 - Income & Expenses
Q4 - Income & Expenses

Hope this makes sense.

PS: I am aware my original code is searching for "xxx", this has since been changed.

 

[My Aswer Is This]

I would be interested to see the code which you said was so slow a sheet change event code normally only activates when a particular cell value is changed.
But lets get on to you button click script you want.

You said:

The change in the chart of accounts is a "yes/no" list. These yes/no responses are transposed to row 5 in the other worksheets.
If the answer to a row is "No" in "Chart of Accounts', then I want the macro to hide the columns in the other worksheets that show "No" in row 5.

If the answer to a Row?

Each row has 16,384 cells

Which cell on row 5​

 

[Pascawali]

I would be interested to see the code which you said was so slow a sheet change event code normally only activates when a particular cell value is changed.
But lets get on to you button click script you want.

You said:

The change in the chart of accounts is a "yes/no" list. These yes/no responses are transposed to row 5 in the other worksheets.
If the answer to a row is "No" in "Chart of Accounts', then I want the macro to hide the columns in the other worksheets that show "No" in row 5.

If the answer to a Row?

Each row has 16,384 cells

Which cell on row 5​

Im pretty sure it was a loop code, but its pretty extensive because it always searched for a sheet change.
Also, its not a particular cell change, its an array change.

Sorry I understand this is confusing.
So basically, in the "chart of accounts" worksheet, there are 43 different "Yes/No" responses in the range "B3:B45".
The answers to each "Yes/No" response are transposed to the range "B5:AS5" in the other worksheets.
So basically, every time a response is "No" in the "Chart of Accounts", the corresponding columns need to be hidden.

Does this make sense?

 

[My Aswer Is This]

The Range("B3:B45") is 43 cells

The Range("

B5:AS5") is 44 cells

The only way I know to do this is to use a sheet auto change event script.

The script runs when you enter "No" into any cell in Range("B3:B45")

I do not know how you would do this on a Button script.

Now the range problem I mentioned above may need to be corrected

Try this:

Now this script only works on Sheet(2) try this and see if it works on Sheet(2) the way you want.
This is not a looping script.
Put this script in sheet named "chart of accounts"

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/8/2019  8:26:14 AM  EDT
If Not Intersect(Target, Range("B3:B45")) Is Nothing Then
Dim r As Long
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "No" Then
        r = Target.Row
        Sheets(2).Cells(5, r - 1).Value = "No"
        Sheets(2).Cells(5, r - 1).EntireColumn.Hidden = True
    End If
End If
End Sub

​

 

[My Aswer Is This]

Now you mentioned Yes No

You said if the value is No then Hide the column
You did not say if the value is Yes unhide the column
If you needed that you would need to say so.