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
689 Views

Similar Articles

[PageSpeed] 51

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:

addition with a maximum value #4
djarcadian Wrote: > Nevermind. Figured it out! > > =IF(SUM(D10+E10)>20,20,(SUM(D10+E10)+F10)) Actually, no... that doesn't work. When D10 and E10 equal less than 2 and F10 brings it over 20 it's fine but when D10 and E10 equal mor than 20 then the results default back to 20. Hmmmmm. What do I do -- djarcadia ----------------------------------------------------------------------- djarcadian's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1587 View this thread: http://www.excelforum.com/showthread.php?threadid=31496 Try: =IF(F10>0,SUM...

compare values in excel list and mark differences?
I need to compare sets of values and generate a list of the differences. I have a list of countries that I need feedback from and I have a separate list that I have received feedback from. Does anyone know how to generate a list of the differences? ...

Sort Treats Empty Cells As Largest Value??
I have several projects that sort data from greatest to least (descending). That is, nothing is considered greater than a million. Of about 4000 rows between a third and a half are blank (no count). I need the maximum values/count at the top of the list, but Excel puts these empty cells at the top of the list. As a result I have to do a lot of moving of large groups of data around to put the empty/blank cells at the bottom of these lists -- a real time consuming pain. Is there a way to have Excel treat empty/blank cells as having lower values than cells with values? I assume I could f...

Question regarding a line chart y axis value.
Is it possible to have, on a simple line chart, the y axis value on both left and right at the same time? Thanks ahead Hi, Yes, maybe the easiest way is to select the series and choose Copy. Select the chart and choose Paste. You have two lines of the same series, but one is hidden behind the other. Select either line and choose Format, Selected Data Series, Axis, Secondary Axis. Select the legend and then the legend entry for one of the two lines and press del. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Bobby" wrote: > Is it possible t...

How to force a cell value to change if Linked cell moves?
XL 2003 Below is a summary of sheet information SHEET NAME CELL AMOUNT LINK Summary by deal B32 4,000,000.00 B32 The (Sub Address) Link "B32" on Sheet "Summary by Deal" is calculated by SubAddress = SHEET NAME + CELL (was B32). If the cell positions on Sheet "Summary by Deal" change then this Link will fail. I realize that I need to re-calculate the Link by re-doing SubAddress = SHEET NAME + CELL (now B33). More important and the challenge: How do I get the CELL value to change from B32 to B33 when the row is inserted in Sheet "Summary by...

using value in 3rd column to decide if outcome is positive or negative
I have 3 columns of single figures. At present i'm using the sumproduc fuction to multiply and total the figures in column A that fall betwee 4 and 9 with the adjacent figure in column B... =SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600) I'd like to add column C to the formula, so that if it contained value of -1, 1 or 2, the sum of the adjacent figures in columns A and appears as a negative number. For example A3= 7, B3= 2, C3= 1 Outcome= -14 A4= 9, B4= 1, C4= 5 Outcome= 9 A5= 3, B5= 2, C5= 2 No sum because figure in column A ...

URGENT : How to get CHECKBOX object value from a Excel Worksheet by a C# Class
As subject... I wanna get the value of some Visual Basic Object (Checkbox) in a Excel Worksheet by calling a C# class / VB.NET class.... May I ask for advice ??? What I got ....only Excel application, workbook, worksheet....and so on... Excel.Application excelApp = new Excel.ApplicationClass(); Excel.Workbook excelWorkbook; Excel.Sheets excelSheets; Excel.Worksheet excelWorksheet ; I am not familiar with c#, however the checkbox objects parent is the worksheet. For example in VBA Worksheets("sheet2").CheckBox1.Value - contains the "checked" indicator You i...

not show value
Hi, I am creating a simple sheet that has repeated formulas in the columns. Maybe like this: Cell C1 =A1+B1 where B cells are a user input. Cell A2 will equal Value in C1 and so on. A B C 1 1 2 2 1 3 3 3 3 3 3 3 3 Is there a way to create incremental values in cells where no value will show until the B cell has had a value placed in it? I am trying to avoid a situation where the last value is repeated through out the columns as in 3 in cells C3:C5. Thanks, Louis Hi! Enter this formula in C1: =IF(B1="","",A1+B...

Cumulative column of values in SmartList Builder
I have a column that has numeric values and I want to create another column that adds and accumulate these values. For example: col 1 col 2 12 12 31 43 5 48 ....... It wanted to know like doing this with SmartList Builder. Thanks. You would probably need to create a SQL view and base the SmartList on the view. -- Charles Allen, MVP "Alejandro Luft" wrote: &g...

Continuall Add ing values in a cell
Hi Everyone- I suspect that this question has been asked before but it is difficult to know how to find the correct thread. That being said, here it goes. I would like to make a cell continually add values entered into them. For example, I have a cell that has a value of $12.37 and I have to add to it a new value as I encounter it, lets say $8.73. As it stands know, I pull out my trusty calculator andsum up the two values and key in the sum into the field. I have a pile of recites that I need to add as they come up. Any help is greatly appreciated. Manuel A. Ayala CAD Concepts...

Script running other scripts return values
hello I've got series of VB scripts that I run manually buy clicking on them, Can't I have one script that runs all of them? I would want to check a returned variable to is one script has a failure, the later one won't get executed. Can't I just use the .Run method? How do I return a value from one script, and read it in the other script? Thanks "Bill" <someplace@somewhere.com> wrote in message news:e72gZtg7KHA.5644@TK2MSFTNGP04.phx.gbl... > hello > > I've got series of VB scripts that I run manually buy clicking on...

How do I match 2 items in excel to return a unique value?
For example, how do I match product codes and warehouses to return an unique price? It is a lot easier to answer a question like that if the rows and columnns data is provided. After all, nobody on this side of the server can see your worksheet. "Stumped" <Stumped@discussions.microsoft.com> wrote in message news:DF1EE951-D4F8-48AA-8040-4305FD948EB5@microsoft.com... > For example, how do I match product codes and warehouses to return an > unique > price? You are way to vague with your post. We need more details of what you are trying to ac...

cell value plus text
I would like to know how to get the value from one cell (C81) entered into another cell (N81) and then have text after that value. For example, if C81=2, I need N81 to have 2 plus text (2 oranges). If this is not possible, I do have the text in the first row and could reference that cell, but then would need to know how to string the two cells of text together (i.e. C81=2 and C1=oranges so N81=2 oranges). Try one of these: =C81&" oranges" Or, using a cell to hold oranges: C1 = oranges =C81&" "&C1 -- Biff Microsoft Excel MVP "Deanna" <...

Best way to find the last value in a variable-length table?
I have a table containing a series of readings, something like this: A B # Volume 15 1 1,000 ml 16 2 915 ml 17 3 830 ml 18 4 745 ml 19 5 660 ml ... I need a way to find the last row of the table, which could have anywhere from 2 to 20 rows. hi, ! if column A has only numbers - a formula outside column A =match(9e307,a:a) - a formula outisde "the range" =match(9e307,a1:a20) hth, hector. __ OP __ > I have a table containing a series of readings, something like this: > A B > # Volume > 15 1 1,000 ml &...

Counting + and
Hi, I have a long column of #'s that consist of positive and negative #'s of differing values. I am looking for a simple formula that will count the number of #'s that are negative and divide that by the number of values that are positive. IS there an easy way? thanks! -- RalphSE ------------------------------------------------------------------------ RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931 View this thread: http://www.excelforum.com/showthread.php?threadid=501185 =COUNTIF(Range,"<0")/COUNTIF(Range,">...

Searching for values
I would like to create a report for the following: 1 - Read a worksheet with data like the following: C1 C2 R 1 - John Smith A R 2 - John Smith A R 3 - John Smith A R4 - Bob Jones 2 R5 - Bob Jones 1 R6 - Bob Jones 12 2 - Produce a report for every occurrence of John Smith: C1 C2 R 1 - John Smith A R 2 - John Smith A R 3 - John Smi...

Finding difference in Month Values
I'm trying to extract differences in month values - eg Values >1 (january) and <4(april) return 11,12 ,2 & 3. How can I get results 2 & 3 or february and march? Thanks to any one that can help with this. Margaret You need to give us more infomation.... post the SQL statement of the query that you're trying to run. I'm guessing, based on the values being selected, that you're Values field/variable is a text data type, which will cause the problem you're seeing. In text data, 11 and 12 are less than 4 because the first "digit" (actually a te...

Making a cell mandatory if another cell has a particular value
Hi, any help would be appreciated! I am trying to create a template where people fill out information about a user. I want cell C6 to be filled out (NOT empty) if cell C4 = "XX" or C4 = "YY". Then, I want to be able to save the template where C6 is blank so that people can fill it out later; Thank you! Lisa I would use an adjacent cell and a formula: =if(and(or(c4="xx",c4="yy"),c6=""),"Please answer C6","") And format this cell in big, bold, red, letters. If you have lots of values to check in C4...

Sum only positive values
Dear all, In my table the values can be positive or negative. I'm trying to do a consult to sum only the positive values... How can I do it? Thanks in advance! Andr=E9. Add the field to the query once more, remove the show flag & enter the condition ie SELECT Sum(MyFieldd) AS TheSum From MyTable Where MyField >0 HhH Pieter <gatarossi@ig.com.br> wrote in message news:1191407027.588326.298520@o80g2000hse.googlegroups.com... Dear all, In my table the values can be positive or negative. I'm trying to do a consult to sum only the positive values... How can I do i...

how to retrieve current values assigned for names used in EXCEL?
I got a spread sheet from some one else. There were formulas used using names. I like to retrieve the values used for different names used in the formulas. Please let me know for any questions. Rao goto <insert><names><define> and you will be able to find the names and either cells, values or formulas assigned to the names. "yrk" wrote: > I got a spread sheet from some one else. There were formulas used using names. > I like to retrieve the values used for different names used in the formulas. > > Please let me know for any questions. > > R...

Changing values in a value box
Hi all, I have a data set that looks like this: DATE A$ B$ A% B% mar-14 50 100 33 66 mar-21 100 100 50 50 mar-28 75 25 75 25 I want to chart the percentages by each date, but I want the "value" box to display the dollars. The only way I can think to trick the Excel chart is create 2 x-axises and hide the $ axis, but then the $ values don't match the height of the % columns. Is there a way to link the $ values to the % columns? Thanks. Josh, To chart only the percentages, select the Date column, and also the two % columns (drag throug...

Finding Location of Maximum Value in 2D Array
I am using MS Office ExCel 2003(11.6560.6568) SP2 and have a single sheet with a 2D array of numbers roughly 202x202 in size. Could someone tell me the quickest way to locate the maximum value in the array? I tried =WhereMax("Sheet1") but just got #NAME? as the result. Any assistance would be greatly appreciated, Peter. Hi Major: Sub findmax() Dim r As Range Set r = Selection v = r.Cells(1, 1).Value For Each r In Selection If r.Value > v Then v = r.Value s = r.Address End If Next MsgBox ("maximum value " & v & " found in cell &...

Re: Total value of invoices created per job
"i_takeuti" <i_takeuti@mx9.kct. ne.jp> wrote in message news:... > > "CW" <CW@discussions.microsoft.com> wrote in message > news:3C0DAC45-A61E-47F3-A7B7-4F89DC2E7D3F@microsoft.com... >>I have jobs built on a Main form with several subforms on tab controls, >>the >> parent-child key always being the Ref. >> I have now added an invoice form (as another subform) and it is working >> OK >> and seeing the Ref and I am saving into the underlying table without any >> problems. >> On some jobs we will raise mo...

Save External Link Values
What exactly is Save External Link Values, and is it necessary for linked files? I keep getting a message that says 'Not enough memory'....'unable to save external link values'. Before I turned it off, I'd like to know what I'm doing to the workbook. The help files don't seem to have anything on it, or it's listed in some obscure way. There's no harm in turning it off. I have it off in all my large data sheets, not for the memory problem, but to reduce the time it takes to open the large sheets on some of the "old" machines around the office. ...

Split database into smaller db based on values
I have a database with multiple tables. I need to break the database into smaller files based on states. I've been able to do this one at a time by using the transfer Database function in a Macro but i'd like to set this up to loop through the different state values in my Locations table and save each new database into a different folder. for example: run query for orders where state = CA then transfer the query data into a table in a file of the same name but directory C:/CA/mydata.mdb then OR C:/OR/mydata.mdb. any help would be appreciated The approach of sp...