Need VBA script to run based on cell calc in separate tab

[Cgalla87]

Hi All,

Here is what I am trying to do. I have a formula in cell E9 in tab TOTAL FINANCIALS. This formula is an IF statement that will result in either 36, 48, 60, or 72 based on what a user selects in a drop down box (cell C11) and a date (cell C9) in a separate tab called FINANCIALS. (I already have script in “financials” tab to hide columns and I want these same columns hidden in “total financials tab”).

If the formula in E9 results in:
36: hide columns F, G, H
48: hide columns G, H
60: hide column H
72: hide no columns

The problem I am having is I basically want the exact same columns hidden in an additional tab but it will not be the “active sheet” I am working in. Any help would be greatly appreciated.

 

[Mike Blackman]

It's not elegant but you could tie this into the TOTAL FINANCIALS Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("E9")) Is Nothing Then
        Worksheets("FINANCIALS").Columns("F:H").Hidden = False
        Worksheets("TOTAL FINANCIALS").Columns("F:H").Hidden = False
        Select Case Target.Value
            Case 36
                Worksheets("FINANCIALS").Columns("F:H").Hidden = True
                Worksheets("TOTAL FINANCIALS").Columns("F:H").Hidden = True
            Case 48
                Worksheets("FINANCIALS").Columns("G:H").Hidden = True
                Worksheets("TOTAL FINANCIALS").Columns("G:H").Hidden = True
            Case 60
                Worksheets("FINANCIALS").Columns("H").Hidden = True
                Worksheets("TOTAL FINANCIALS").Columns("H").Hidden = True
        End Select
    End If

End Sub

 

[Cgalla87]

Hi Mike.

Thanks for the help. I had a question. I have the original script for my “financials” sheet in that tab. Would I put this script into the “total financials” tab?

Also, I have a worksheet_calculate event, would your script also need to be the calculate event? Below is the script I have in the original sheet.

Private Sub Worksheet_Calculate()
With Activesheet
.Columns.Hidden
Select Case.Range(“E9”).Value
Case 36
.Range(“F:H”).EntireColumn Hidden=True


Etc with the code for the other criteria.

 

[Cgalla87]

Maybe below I explained better what I need.

I have a workbook with 13 sheets. The first two sheets are “financials” and “total financials”. I need a script to run in both of these sheets based off of one cell formula result, that cell is E9 in the “financials” sheet. Once a drop down is selected in that sheet cell E9 will return a result of 36, 48, 60, or 72. I need the exact same columns to be hidden in both of these sheets concurrently once that formula calculates.

If 36 hide columns F:H
If 48 hide columns G:H
If 60 hide column H
If 72 hide no columns.

Is there a script to get this to run in both sheets at the same time? If so, is that script input into the “total workAny help is greatly appreciated.

Thanks!