Automatic Macro: Remove the click requirement?

[SvelteRose]

I installed an automatic macro but it only updates if you click on the worksheet. How do you make it so that the macro works instantly and not because you have to click it?

Here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("H8").Value = 0 Then
        Columns("H:L").EntireColumn.Hidden = True
    Else
        Columns("H:L").EntireColumn.Hidden = False
    End If
    If Range("M8").Value = 0 Then
        Columns("M:Q").EntireColumn.Hidden = True
    Else
        Columns("M:Q").EntireColumn.Hidden = False
    End If
    If Range("R8").Value = 0 Then
        Columns("R:V").EntireColumn.Hidden = True
    Else
        Columns("R:V").EntireColumn.Hidden = False
    End If
    If Range("W8").Value = 0 Then
        Columns("W:AA").EntireColumn.Hidden = True
    Else
        Columns("W:AA").EntireColumn.Hidden = False
    End If
    If Range("AB8").Value = 0 Then
        Columns("AB:AG").EntireColumn.Hidden = True
    Else
        Columns("AB:AG").EntireColumn.Hidden = False
    End If
End Sub

 

[SvelteRose]

The XVals is not working. These are my named formulas:

EndYr =0+MID(SummaryDB!$B$5,FIND("-",SummaryDB!$B$5)+1,FIND(")",SummaryDB!$B$5)-FIND("-",SummaryDB!$B$5)-1)

StartYr
=0+MID(SummaryDB!$B$5,FIND("(",SummaryDB!$B$5)+1,FIND("-",SummaryDB!$B$5)-FIND("(",SummaryDB!$B$5)-1)

Then when I try to put in my XVals:

=SummaryDB!$C$8:INDEX(SummaryDB!$8:$8,MATCH(EndYr,SummaryDB!$8:$8,0))

The chart won't accept it. It says that my formula contains an error. Also, what are the "-" looking for? Why does it say FIND(")?

 

[tusharm]

What do you get if you enter =EndYr in some cell?

Does not B5 contain ({start year}-{end year})? The FINDs and MIDs are parsing out the 2 years.

The XVals is not working. These are my named formulas:

EndYr =0+MID(SummaryDB!$B$5,FIND("-",SummaryDB!$B$5)+1,FIND(")",SummaryDB!$B$5)-FIND("-",SummaryDB!$B$5)-1)

StartYr
=0+MID(SummaryDB!$B$5,FIND("(",SummaryDB!$B$5)+1,FIND("-",SummaryDB!$B$5)-FIND("(",SummaryDB!$B$5)-1)

Then when I try to put in my XVals:

=SummaryDB!$C$8:INDEX(SummaryDB!$8:$8,MATCH(EndYr,SummaryDB!$8:$8,0))

The chart won't accept it. It says that my formula contains an error. Also, what are the "-" looking for? Why does it say FIND(")?

 

[SvelteRose]

I get $1,990.

No, it is not an equation. It says "Actual (1981-1985)". Would it be better to reference it as a number instead of years? I can do 5, 10, 15 for 5 yrs, 10 yrs, 15 yrs, etc.

Edited to say I'd rather not have it be an equation anyway. I can just make it 5, 10, 15 yrs in B6 and we could reference all coding to B6.

 

[SvelteRose]

I forgot to mention, the actual text I have in there currently is "Actual (1981-1990)" so that's why it pulls up 1990. If I change it to "Actual (1981-1985)" it pulls up 1985.

 

[tusharm]

Then, the only thing I can think of is that the values in row 8 are not numbers but text. In the named formula EndYr, remove the 0+ and see if that works.

I forgot to mention, the actual text I have in there currently is "Actual (1981-1990)" so that's why it pulls up 1990. If I change it to "Actual (1981-1985)" it pulls up 1985.

 

[SvelteRose]

Then, the only thing I can think of is that the values in row 8 are not numbers but text. In the named formula EndYr, remove the 0+ and see if that works.

It doesn't work because row 8 is being driven by formula. C8 is 1981 while C9 is C8+1 and so forth. Index/match won't pick it up because it's not reading 1985 but the formula instead.