Enable Editing Chart But Not Source Data?
Mar 18, 2014is it possible to unlock a chart but then disabling to change the source data?
Background: I want the user to be able to add numbers etc. but he shouldn't see the underlying table...
ADVERTISEMENT
Error Message When Editing XY Chart Dynamic Source Data?
Feb 9, 2012I am trying to create a simple xy graph in Excel 2010 using dynamic source data.
I have data in three columns.
There are headings on line 2 and the data starts on line 3.
A B C
1.
2. dp ss ff
3 .1 .2 .3
4 .3 .4 .4
5 .4 .8 .7
6 .1 .6 .8
7 .3 .5 .1
8 .5 .4 .4
I want to create an XY chart with C3 to C8 as the X axis (or dynamic to what ever the last data point is) with A3 to A8 as the Y axis (or dynamic to what ever the last data point is).
I have Defined names for C3 to C8 (myXvalues) and A3 to A8 (myYvalues).
I have created a simple XY graph and selected the ranges as normal.
This is =DATA(,Data!$C$3:$C$9,Data!$A$3:$A$9,1)
The page is named DATA
What I am trying to do now is to edit this range to make the range dynamic.Unfortunately I get error messages which ever way I try to edit the range.
I have tried:-
=SERIES('Data'!$c$3,'Data'!myXvalues,'Data'!$A$3,'Data'!myYvalues,1)
=SERIES('Data','Data'!myXvalues, 'Data'!myYvalues,1)
=SERIES(,Data!myXvalues,Data!$myYvalues,1)
=SERIES(Data!,Data!myXvalues,Data!myYvalues,1)
The data range can go from 8 to 30000 hence the need for a dynamic range!
PS: Also, is there any way to create this graph and dynamic source data in VBA. I have tried this with no success.
Worksheet Protection From Editing And At The Same Time Allow All Data Source Links Within The Sheet To Be Refreshed
Mar 16, 2007how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies View RelatedCan't Make Enable Editing Stick
Jun 19, 2014I have a spreadsheet sent in an email. When I open it there is a warning bar saying
"Protected View This file originated from an Internet location and might be unsafe. Click for more details." then a button with Enable Editing.
I click the button and can edit the spreadsheet and save my changes. But the next time I open the file I get the same warning again. What is the way to designate that this spreadsheet is safe and prevent the warning recurring whenever I open the file?
Prevent Editing After Date And Force Enable Macros
Apr 7, 2014So I have a Workbook that I want to make read-only/protect after a certain date. I also want to force the user to have macros enabled. So far I have the following script, but despite changing it to read-only it doesn't seem to do anything. I have also tried to enable protection on the sheets after that date, but am getting errors.
View 2 Replies View RelatedEnable Mouse's Right Button Menue & WS Editing While Userform Running
Dec 5, 2009I have a User Form which i want to use in such a way that editing the worksheet directly is not disabled while the userform is running. I would also like to use the right button mouse menu(cut, cpy,paste) while i am using the user form. I am using two Monitors with my PC and i want to work with User Form on one monitor being able to edit worksheet without having to close my User Form.
View 2 Replies View RelatedSet Source Data For Chart Using R1C1
Mar 9, 2014Everything else in my macro is using R1C1 format for ease of automation. However, I cannot get this to resolve. I am setting the source data for a line chart.
However, if I use A1 format it works just fine.
I am using R1C1 for automation purposes. A1 format will not easily work. I have the interpolated statement in there because the range contains some empty cells. I want excel to fill the line in the chart using interpolation. All the data in the columns(N through R or 14 through 18 in R1C1 format) is contiguous. I have not tried Union, but it seems I should just use this range like I do in the A1 format.
Manipulating Chart Source Data With VBA
Aug 8, 2007I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart).
What commands to I need to enter into VBA to get this to work?
I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be.
The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7.
The current values that these cells hold are:
L6:
=Graphics!$J6:$J9
L7:
=Graphics!$K6:$K9
Oh, and the name of the chart is "Chart 1"
What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?)
If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.
Chart Not Reflecting Data Source
Jun 29, 2006In my table I have rounded my values to a particular number of decimal places. BUT, the bars of my chart have the length of the original numbers. This is especially ridiculous when in the table there are only (rounded) zeros, and in the chart I get several bars with different length. how to plot the bars in the same length as the numbers in the table are?
View 9 Replies View RelatedChart: Selectable (ddl) Data Source
Aug 10, 2006I have a sheet with logged test data.
Various test items are stored in columns.
(First row of each column is the description of the test item)
Problem:
1) To reflect certain testdata in a chart I want to be able to select a different column (Y values) by means of a dropdownlist.
X values are just the number of items (1 to ...)
How to make the charts data source selectable?
2) It seems that the input range of a ddl should be a list of items.
In my case they are in the first row of each column so only the first item is shown.
Chart With Changing Source Data
Aug 11, 2006I am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.
View 2 Replies View RelatedDynamic Chart Source Data
Jul 27, 2007In a workbook that I am working on, a macro runs to import data onto 9 separate sheets. The data is essentially x- and y- coordinates of a plot profile from another program. The problem that I am running into is that the number of data points can vary each time the data in imported based upon how the data was acquired.
If I were to put a drop-down list (Method1, Method2, Method3, etc.) on the Instructions page that I have for this workbook and have the user select which method was used prior to running the macro, could I then incorporate some sort of IF statement in the macro to vary the Source Data Series for the charts that I have created? Right now, I have the Source Data covering the scenario with the longest list of data points so when fewer points are used, the graph looks essentially useless.
Change Chart Source Data From Add-in
Aug 9, 2007I have an excel addin that contains chart templates. Without going into too much detail a user will select an option which will update these templates then move the charts along with the source data to a new workbook. My question is this; How do I change the chart references programatically so that the chart data now points to the source data in the new workbook.
View 5 Replies View RelatedColor Chart Data Points By Cell Values Of Non-Source Data
Apr 14, 2008I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies View RelatedChart Source Data: Union Of Every N Cell
Sep 19, 2006I have 4 sheets. In each sheet, I have a column of data for every hour of every day of one year.
In the 1st worksheet, I want to make a chart using :
- for Y values : the average of the data for each day
- for X values: the day
In the 3 other worksheets,
- for Y values : the data for each hour
- for X values: the hour
To make it more simple, I will first build in the "D" column the data of the average data for each day (so, every 24 columns)
Here is my
Dim lNbSheet As Long ' sheet number
Dim rSheetData As Range ' what will be my Y values
Dim rSheetTime As Range ' what will be my X values
Dim sSheetName As String ' current sheet
For lNbSheet = 1 To 4
'selection of the Y values
Sheets(lNbSheet).Select
sSheetName = ActiveSheet.Name
I get the error "methode SeriesCollectiosn of Object _chart failed;
Also, if you have an idea that could make me avoid writing all the new average-for-the-day data in column D (using it directly),
Graph & Chart: Data Source Changes Monthly
Nov 19, 2006I am trying to write a macro that will automatically change the source data for a graph. This is an existing graph that is on tab GraphYTD and the data for the graph comes from DataYTD. The number of rows of data will change monthly, but I would like to use the same graph template every month. Here is my
Sub SortYTD()
Dim myBottom As Long
myBottom = Sheets("DataYTD"). Range("B65536").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("DataYTD").Range("F2:I" & myBottom)
ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphYTD"
End Sub
I am getting Runtime error 91 - object variable or with block variable not set.
Link Spinbutton To Chart Data Source
Nov 21, 2006For example, if my graph currently shows B1:L1 range, how by clicking a spin button to make the graph show B2:L2 range?
...
Changing Chart Source Data Range
Nov 30, 2006Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.
View 3 Replies View RelatedCode To Change Chart Source Data
Jul 16, 2007I am in the middle of building a macro for some data to be automated. I have two sheets, one of which has an existing chart but the data has been removed so now there is no values on the chart. I then have a second sheet with data on it. The range is b4:c16. I want to point this data onto my chart on the other sheet.
View 2 Replies View RelatedVBA - Retrieve Chart Series Source Data In Range?
Jan 13, 2010Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?
This is what I've written so far:
Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer
[Code]....
As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.
Combination Chart: Present Source Data Differently
Sep 27, 2006I've taken over a spreadsheet and been asked to produce various charts. One chart in particular asks for views of the following (hyperthetical) data from different workbooks on the same sheet
July 06 - 24%
June 06 - 22%
May 06 - 29%
April 06 - 21%
overlaid with this data
April 06 - 42
May 06 - 68
June 06 - 47
July 06 - 55
The problem I'm having is that firstly, the dates are presented the opposite way round, and secondly one set of data is in percentages, the other in basic integers. The spreadsheet data is large, and historically has always been done like this so it's not easy to change the way its presented, but is there an easy way to show it in a combination chart?
Copy Chart Without Source Data & Retain Formatting
Feb 21, 2008I have 2 sheets in one workbook; one sheet has a chart with a data table, the other contains the source data for the chart. I want to copy and move the sheet with the chart to a new workbook, but without the source data. I break all links, but then the chart's data table takes the default values and formats in the new workbook after I break the links. For examples, the source data has formats such as currency and no decimal places. However, after copying the sheet, the data table reverts to no dollar sign and decimal places. In addition, the months on the x-axis turn to numbers when the links are broken.
View 3 Replies View RelatedDynamic Named Range As Chart Source Data
Jun 16, 2008I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:
Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub
I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.
Sorting Chart Legend To Match Source Data Order
Feb 23, 2012The order of entries in my chart legend make no sense to me. Why is it not matching the order of the source data?
View 1 Replies View RelatedChanging Chart Source Data Based On Running Date
Apr 4, 2007I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
Copy Chart To New Sheet & Have Charts Source Data Reference It
Jan 24, 2008Sheet1 contains a chart using a dynamic name in Source Data: Values. The dynamic name data is controlled by another cell. When I copy the sheet - i.e. Sheet1 (2) - and change the control cell, the Source Data: Values in the copied sheet still refers to the original Sheet1.
View 3 Replies View RelatedAutomatically Adjust Dynamic Chart As Data Added To Source
Jun 19, 2008Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?
View 3 Replies View RelatedAdd Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart
Apr 28, 2009I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedVba Chart Axes Editing Syntax
Jul 12, 2007my VBA code does some simple editing. Something I would like it to do is make the x-axis and y-axis lines be editable. What I mean is say for editing the Plot area background you would use this
ActiveChart.PlotArea.Format.Line.ForeColor.RGB = RGB(0, 0, 0)
I am looking for the equivalent to do the x and y axis lines.
Pivot Table Retains Old Source Data In Addition To New Source Data
Sep 7, 2006I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon