Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Possible to highlight a specific vertical axis gridline?

Posted on 2014-03-17
7
Medium Priority
?
200 Views
Last Modified: 2014-03-18
Hello - I am developing a pivot chart on employee rewards data for a company, and they want to see if the divisions are meeting a 5% rewards goal for each quarter, and in line to make a 20% goal for the year.  

I’ve stumbled upon the right object references to set the chart title when the user changes the “Qtr” chart filter, but what I really need to do is highlight (red, bold) the 5% vertical grid line when the user selects Qtr 1, 2, 3 or 4, and highlight the 20% gridline when they select the “All” value for year-to-date.

(screen shots posted, I am familiar with VBA, this is Excel 2010)

Is this possible?

Thanks
GRR-Chart-Q1.png
GRR-Chart-YTD.png
0
Comment
Question by:mlagrange
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39934011
Hi,
You could add a field wit =0.05 in the formula and chane the chart type of this serie

then when changing the filter of the pivot change the formula to =0.2

EERegards
0
 

Author Comment

by:mlagrange
ID: 39934951
Great - can you please tell me the object reference code to set that formula?
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39935032
Hi,

 to set the formula.

pls use

 ActiveSheet.PivotTables("PivotTable1").CalculatedFields("Field1"). _
        StandardFormula = "=0.2"

Open in new window

Regards
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mlagrange
ID: 39935048
Does that need to be followed by some kind of Refresh or something? when I step through that in debug mode, it executes that statement, and then loops back up to the "Private Sub" line (the top of the procedure)
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39935088
No it does the change directly as far as I know
0
 

Author Comment

by:mlagrange
ID: 39937029
ok, figured it out - setting this formula in VBA actually fires the Worksheet_PivotTableUpdate() event itself! That's why it kept jumping up to the top of the proc after .StandardFormula line

Then it was talking 2 updates of the quarter filter (selecting the same value, twice) before it actually refreshed the chart. So I added a .PivotCache.Refresh

So here is the code:

    Application.EnableEvents = False
   
    Select Case ActiveSheet.PivotTables("pvtL1").PivotFields("AwardQtr").CurrentPage
        Case "Q1", "Q2", "Q3", "Q4"
            ActiveSheet.PivotTables("pvtL1").CalculatedFields("GoalLine").StandardFormula = "=0.05"
        Case Else
            ActiveSheet.PivotTables("pvtL1").CalculatedFields("GoalLine").StandardFormula = "=0.20"
    End Select
   
    ActiveSheet.PivotTables("pvtL1").PivotCache.Refresh
       
    Application.EnableEvents = True
   
 Thanks for your help.

(PLEASE take a look at the question I posted this morning:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28390967.html
0
 

Author Closing Comment

by:mlagrange
ID: 39937037
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question