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

Cgalla87

New Member
Joined
Nov 29, 2018
Messages
16
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
It's not elegant but you could tie this into the TOTAL FINANCIALS Change event:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top