Toggle Data Label "Value" On and Off

Is there any way to toggle the "Value" label for a chart on and off
using a button linked to some VBA code.  I'd like to be able to have
the label formatted (i.e. color, size, etc) so that the user just has
to click a button for the values to show on the bars of the chart.
Thanks for any help you can give!

-Josh

0
jgrappy (2)
6/7/2006 2:38:57 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
538 Views

Similar Articles

[PageSpeed] 20

Found my own answer through recording macros.  Here is the code I used,
applying it to a toggle button.

Private Sub ToggleButton1_Click()
    Application.ScreenUpdating = False
    If ToggleButton1.VALUE = True Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True,
LegendKey:= _
        False, ShowSeriesName:=False, ShowCategoryName:=False,
ShowValue:=True, _
        ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(2).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
        .Background = xlAutomatic
    End With
    Windows("GanttChartTemplatewc.xls").SmallScroll Down:=3
    ActiveWindow.Visible = False
    Windows("GanttChartTemplatewc.xls").Activate
    Range("Q36").Select
    ActiveWindow.SmallScroll Down:=-6
    Else
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True,
LegendKey:= _
        False, ShowSeriesName:=False, ShowCategoryName:=False,
ShowValue:=False, _
        ShowPercentage:=False, ShowBubbleSize:=False
    Windows("GanttChartTemplatewc.xls").SmallScroll Down:=3
    ActiveWindow.Visible = False
    Windows("GanttChartTemplatewc.xls").Activate
    Range("N36").Select
    ActiveWindow.SmallScroll Down:=-6
    Application.ScreenUpdating = True
    End If
    End Sub

0
jgrappy (2)
6/9/2006 6:52:30 PM
Reply:

Similar Artilces:

2nd to last value in column
Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg Try the below to get the data from the cell right above that? ...

Data Values
Can anyone tell me how to create my data values to show the total of the data points instead of each individual data point? MS wrote: > Can anyone tell me how to create my data values to show > the total of the data points instead of each individual > data point? Are you attempting to label each point of the chart with a total? If so, you can create a helper cell (or cells) with the total that you want displayed on the chart. Then use the XY chart labeler from www.appspro.com to label your data points with the cells you just created. Dave dvt at psu dot edu I will look into ...

Validate for Null value for multiple column
I have requirement to validate for Null value which compares Column A & B entry in excel sheet. I have tried by setting IgnoreBlank = False, but no success. How do I validate for Null value in either Column A 0r B. url:http://www.ureader.com/gp/1027-1.aspx ...

Finding value in the last cell in column A
Hi I have this expanding Excel-database, and on all occasions I want to use the latest (max rowindex) value of the datainput in column A. I'm certain this is a-piece-of-cake, but what kind of formula is to be used? Kindly regards Snoopy Try either: =LOOKUP(99^99,A:A) will show the last number in column A =LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will show numbers or text. Hope this helps "Snoopy" wrote: > Hi > I have this expanding Excel-database, and on all occasions I want to > use the latest (max rowindex) value of the datainput in column A. > I...

Annoying & unrecognised value
Hoping someone may recognise this. I regularly import data in to excel for use with various look up / count / if functions. Quite often I find that where I have pasted in values that they are not recognised until I go to the specific cell and hard return the value. I have tried cell format; general, number, text ect but makes no difference. I normally use paste special, values only so as not import any format or formula. Can be a real pain on large data sets........... mouse point, enter, next cell, mouse point, enter, next cell, ...... Ay ideas ? D:-) -- Digory ------------------------...

Filtering Combo #2 from a value in Combo #1
Hi, I have two Combo fields: cmb1 (which has a choice of PROD / DEV) cmb2 - I want to list dates associated either PROD or DEV. I have this SQL for cmb1: strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType, tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE tbl01_FullCompare.ShutType = "" & Me![cmbShutType] & "" ORDER BY tbl01_FullCompare.ImpDate;" Me![cmbImpDate].RowSource = strSQL However when I i select the cmbImpDate drop down box, there are no records listed... can anyone advise how to get this working? Any assistanc...

Is it possible to use 'grid' as a value in vb to process XL data?
I was processing the Excel data that has 'grid' as a value, When I used the VB macro to process that particular row was missing. When I renamed this to 'grid1' the result appears with 'grid1'. Is there any reason for this? Philip ...

Validation based on the value in another cell
Hi all, How can I base the validation in one cell on the content of another cell? Example: If value is cell C9 = "Level 1" I want the validation in cell A3 to force the user to enter a value equal to or greater than "8000" If value is cell C9 = "Level 2" I want the validation in cell A3 to force the user to enter a value equal to or greater than "4000" Thanks. Carlos What if C9 is neither "Level 1" nor "Level 2" ? -- Biff Microsoft Excel MVP "supersub15" <carlos_chalhoub@hotmail.com> wrote in message news:4d...

Data Validation #VALUE
Even though I have data validation to accept only whole numbers, if a user accidentally enters text, other cells that contain formulas based on this cell change to #VALUE. The text is not allowed to be entered in the cell, but the formulas in the other cells apparently read that text. Don't the #value's only momentarily show up. Then you get the warning message from the Data|Validation check. Then as soon as you hit Retry or Cancel, the #values turn back to the previous value? I wouldn't worry about it, but you could change your formulas... =if(istext(a1),"",your...

Error retrieving remote system values
hi, i am trying to retrieve remote system attributes using WMI but the problem is i'm not getting return values. please find the code snippet below. please let me kno what went wrong.. thanks, NKH IEnumWbemClassObject* pEnumerator = NULL; hres = pSvc->ExecQuery(bstr_t("WQL"), bstr_t("Select * from Win32_OperatingSystem"), WBEM_FLAG_FORWARD_ONLY | WBEM_FLAG_RETURN_IMMEDIATELY, NULL,&pEnumerator); IWbemClassObject *pclsObj; ULONG uReturn = 0; while (pEnumerator) { HRESULT hr = pEnumerator->Next(WBEM_INFINITE, 1, ...

copmare cotents of two columns and return dissimilar values
Hi All, I want to compare the contents of column A with Coulmn B and to get dissimilar values. I will be thankful for your kind information. In cell C1 enter =if(A1=B1,"","dissimilar") and copy down -- Gary's Student "Shaukat Hussain" wrote: > Hi All, > > I want to compare the contents of column A with Coulmn B and to get > dissimilar values. > I will be thankful for your kind information. ...

Count if date is before value?
I have a roster of employees and there is a column for the date of thei last annual training. I am trying to have a total on the bottom of th column that counts how many people are delinquent (havent had the clas in over a year) in their training. Looks like this: Name Class Date John Sexual Harassment 4/15/2003 Harry Sexual Harrasment 3/17/2003 Don Sexual Harrasment 4/1/2004 TOTA REQQUIRED 2 2 would be the total if the date was set to 4/15/2004 I have tried COUNTIF(C2:C107">4/15/2004")...

Look & return values, in cells with numbers & words
Hello I have a problem in a table that has words and numbers: ex.-> Puxador 3584.251 Jkf I want that it reads only the 3 last numbers and returns to onother cell the designation: ex.-> Puxador 3584.251 Jkf &( Designation i read and recognize as 251) How can i do this? Thanks in advance Hi If the three numbers you are wanting are always preceeded by the only full stop (period) in the cell you could use: =MID(B10,FIND(".",B10)+1,3) to return them. It really depends on if your cells are uniform in content. -- Andy. "Micos3" <Micos3@discussions.microsoft.co...

How do I change default attribute values from with in SQL Metabase
I need to change a bunch of bit defaults from yes to no and was hoping to do it from within Enterprise Manager quickly instead of going in and out of every field withing CRM. Anyone know the table I need to look at where this is stored? I looked at the attribute table in the metabase which seems to have a column called default value but this does not store the default as I looked at default settings of two bit fields I have one set to yes and one set to no and in the attribute table there is no difference that I can see. -- Thanks, Brian Updating the tables on the back end is not supp...

Display only duplicate values and delete UNIQUE Items #2
I have large excel file - which has duplicate items in, I would like to compare col A and Col B and if any of the items in apear in that combination anywhere else in the colums then i want either a helper col to show its is a duplicate or to highlight it in a different colour. Ideally as as this will be done by may people ( who will not follow instructions on how to do this ) i would like for them to just open up a file and then press on a button .... Eg: A | B| Help Col 1 | 5 | Duplicate 2 | 6 | 3 | 7 | 4 | 9 | 1 | 5 | Duplicate 1 | 5 | Duplicate Any suggestions? I can suggest a way to...

Excel drop-down values versus labels
I can't figure out how to duplicate Web-based Select boxes, as drop-down lists in Excel. I can create the drop-down list using Data | Validation | Settings, selecting from Allow the opion List (Ignore Blank and In-Cell dropdown checked), and then enter into Source: Standard, Large, Extra Large (each item separated by commas). But I can't figure out how to calculate which item is selected without duplicating the data, It seems inefficient to duplicate "Standard, Large, Extra Large" elsewhere, and do a lookup. 1. Is there not a function which will return the Nth it...

Enter Different values in Project Server and get in Reporting
I want to know actually I will create a report in reporting tool. I want to enter different values which may be numeric and text and may be select from drop down list which i will enter in my project at project professional . and when the report will be create in reporting tool. the values which i entered in my project may be in project information dialog box and other way will be display at report. my first question is How I can enter different values which for my project in project professional. and these values will be at published database or in reporting database or I ...

Changing the values in a column
I have several thousand rows in my spreadsheet. My question is that I want to look at the value of one column and based on its value, change the value of another column. I would I do this? -- Dirk From what to what? -- HTH Bob Phillips "Dirk_Bob" <DirkBob@discussions.microsoft.com> wrote in message news:0C8575BA-0D12-43B2-A3AA-927E662F1B77@microsoft.com... > I have several thousand rows in my spreadsheet. > My question is that I want to look at the value of one column and based on > its value, change the value of another column. > > I would I do this? &g...

macro to hide columns with zero values?
I'm relatively new to VBA and need to create a macro to hide columns with zero values. The workbook has 36 sheets, each sheet has columns B:CR and 202 rows. Row 202 is the sum of the respective column. I'd like to run the macro in a separate workbook so I can use it for other similar workbook situations. Any suggestions would be greatly appreciated! I believe the code below will work for you. It will examine all used cells on the row on a selected sheet where a cell is selected and hide all columns with an empty cell or a zero value cell on that row. In your example, ...

Importing MS Office Excel Comma Separated Values Files into a MS Access 2003 database
Hi, I have been tasked with Importing MS Office Excel Comma Separated Values Files into a MS Access 2003 database. These files were exported from a DB2 database.I have also been given the DDL code. I have Googled a solution to this task and have tried creating these tables using the DDL code in a query within a blank MS Access database. Is there a more expedient way to achieve this? I noticed, for example, that a field in DB2 has a data type of "decimal" while the same field would have the data type of "number". Thanks, Zuf What follows is the DDL: -------...

How do I change FALSE values to 0?
How do change values of TRUE or FALSE to 0? what is the formula you are using? "Redleg40" wrote: > How do change values of TRUE or FALSE to 0? =IF(AD7<0,AD7*-1) & =IF(AD13>=0,AD13) "Jambruins" wrote: > what is the formula you are using? > > > "Redleg40" wrote: > > > How do change values of TRUE or FALSE to 0? Put a 0 in for the value_if_false argument. Standard IF syntax =IF(condition,value_if_true,value_if_false), so for your first formula =IF(AD7<0,AD7*-1,0 -- MrShort ----------------------------------------...

comparing values from one workbook with another workbook
I have 2 workbooks. workbook1 contains a list of employees, emp# and date of hire. Workbook 2 has a differnt list of employees. I need to compare the emp# in book1 against all emp# in book2. If the emp# from book1 matches the emp# in book2, I need to copy the date of hire from book1 into the appropriate cell in book2. I must compare the entire contents of book1 against book2. Hi, I assume employee # is in colum A and the date fo hire in column B so in book 2 enter =index(book1!$B$1:$B$10000,match(a1,book1!$A$1:$A$10000,0)) copy formula down, change range to fit your n...

Please Help!! Values Not Updating from linked source
I have a problem with a couple of spreadsheets. When I open the master sheet I get the prompt to update or not. If I choose either the values do not get updated. If I open the other spreadsheet that my formula is pulling the data from the values update. Please Help John Whitmer ...

Data Range with more than value
I am trying to assign codes to one of my worksheets based on pricing. The problem that I am running into is that the data that I am taking my information has a range of pricing Example below PRICE POINT/RANGE PP $0.01 TO $1 PP0100 I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to a column that has pricing (i.e .95). This formula/function should give me the "PP" code. Break the range up into two columns (Data - Text to columns). Assuming you don't have gaps/overlaps in your price range (why would you?) your formula ...

How to change the colour of a cell(s) based on cell value
Hello guys, I am stuck on this rather simple problem. Could not find any hel searching Google. I want to change the colour of a cell to green if the vallue is = Tru and to red if it is False. I have tried to use the Conditional Formating but without joy. Thank you -- Artful Dodge ----------------------------------------------------------------------- Artful Dodger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1656 View this thread: http://www.excelforum.com/showthread.php?threadid=32059 Hi select the cells choose format / conditional formatting choose cell...