Chart Hyperlinks: Attach A Macro So That When The Chart Is Clicked It Returns To Sheet - Home
I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Odd Chart: Combine Data From Multiple Worksheets And Make A Chart
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period). So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
View Replies!
View Related
Combine (Overlay) Bubble Chart & Point Scatter Chart
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart. My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
View Replies!
View Related
Line Chart - 12 Month Chart Moving With Dates
Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting. The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
View Replies!
View Related
Pareto Chart, Set Line Chart Origin At Zero
I have a pareto chart(Bar & Cumulative Line Chart) that I would like to have the line orginate at x = 0 & y = 0 without changing the position of the other points on the line chart. Basically this is for better visual appearance. When I add a 0 to the table, it skews the alignment of the line chart with the corresponding bar chart.
View Replies!
View Related
Chart Making- Chart With 3 Data Series
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000. In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle. My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View Replies!
View Related
Convert Scatter Chart To Bubble Chart
I would like to combine / overlay a bubble chart and scatter chart with straight connectors. I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart. Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment). My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
View Replies!
View Related
Line Chart To XY Chart And Vice-Versa
Attached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?
View Replies!
View Related
Bar Chart And Overlaying Scatter Chart
I'll attempt to explain what I'm hoping to do. I have a column of data that I want to plot out into a chart. For example the data points might run from -1.50 to 2.5. Let's say there are 20 data points in the column and they are spaced thruout the range shown above. They tend to cluster amoung themselves into 2 or 3 distict groups. I want to first produce one horizontal bar that runs from the -1.50 to 2.5 overall range and secondly, I want to overlay a scatter chart horizontally over the bar showing where the data points lie within the range and also so I can visually see the clusters of points. With the data residing in a column, when I generate the scatter chart, the points are laying verticle within the chart and spreadout horizontally. I can manually get what I want by narrowing the chart down to the point that the points lie on a straight vertical line, but I haven't been able to figure out how to "rotate" the chart 90 deg to overlay the scatter points within the bar chart. Naturally, the optimum solution would be such that I could generate the charts originally and then allow the spreadsheet to generate the new charts as we modify the data values, etc.
View Replies!
View Related
Change Chart Series Pasted From Another Chart
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
View Replies!
View Related
A Chart Will Columns And A Line Chart Below??
I can create a column chart in excel comparing sales in 06 v's 07 for each month in 07, what I'm aiming to show on the chart is how sales have grown or not over a comparative period. A simple enough graph, I end up with about 24 columns (2 for each month and then a gap between each month). I now want a line chart to show the performance of an index over the same time period, lets say the FTSE100. I don't know how to get the line chart under the columns on the same graph, using the same x-axis with a small y-axis to the left. I could propably figure out the y-axis bit if I could only get the line chart below the columns.
View Replies!
View Related
Convert Chart Into Pivot Chart
Please look at the sample workbook below. The chart you see below is fine BUT I would like in a pivot table chart, so I could select any days on the pivot chart. I have tried it to do it myself but for some reason the time is not displaying on the pivot chart correctly. Any help please ? P.S. If not possible then can we add a scroll bar or something?
View Replies!
View Related
Pivot Chart Changes Chart Upon Refresh
I have created a pivot table and chart. The pivot chart consists of three sets of data from the pivot table. Two of the data sets are lines and the third data set is displayed as columns. I made the third data set into columns by right clicking on the data series line and selecting chart type. I change the chart type for that data set to a column and it worked great. The only problem is that when data is refreshed in the associated pivot table, the entire chart turns into columns with completely different formatting. Does anyone know how I can maintain the graph with the two lines and one column data set when I refresh the data?
View Replies!
View Related
Transfering Chart From One Sheet To Another
I have created a chart with dynamic ranges. Ranges are defined for that particular worksheet. However when I want to paste that chart on another sheet, chart formulas are not updated for the new sheet. I have similar ranges in each worksheet. I want be able to paste the chart on any of the sheet and change chart ranges/formulas with minimum work. Is there any way to do that automatically?
View Replies!
View Related
Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart: 1) Weather data (temperature) by region: So one region, would be: Northwest I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month. 2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region. 3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data). What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View Replies!
View Related
Reference Chart By Name That Is Not On Active Sheet
I am trying to select cells, tables and charts by just knowing their name. Thus if possible I would like to only refer to a chart by its name i.e. not know what worksheet they are on. This seems to work for named ranges of cells/tables, but not charts (I can't get my named charts to appear in the "Define Name" dialog). I found this code that loops through charts on the activesheet and shows the names. However, I would much prefer not to have to specify the sheet or to loop through all the sheets trying to locate the one with the named chart. Public Sub GetEmbeddedCharts() Dim myChart As ChartObject Dim myCharts As ChartObjects Set myCharts = ActiveSheet.ChartObjects For Each myChart In myCharts Debug.Print myChart.Chart.Name Next End Sub
View Replies!
View Related
Changing A Chart Source Sheet
I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A). I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc. The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B Is there an easy way to change the source sheet for the new graphs?
View Replies!
View Related
Display The Chart Sheet By Clicking Buton
I have designed a software in excel/vba and when I run that project then startup form appears which has various buttons on it and they perform different functions. My problem is when I click on button "View MI Report" at first out of various buttons then it works fine and displays me the excel chart sheet. But If click on any other button first and then on view MI report button then startup form disappears and a file is created at the desktop and then after few seconds startup form again appears but it doesn't display the excel chart sheet. I have written the following code on the button Private Sub CommandButton5_Click() For Each wssheet In Worksheets If Not wssheet.Name = "chart1" Then wssheet.Visible = xlSheetVeryHidden End If Next wssheet UserForm4.Hide Sheets("chart1").Select end sub
View Replies!
View Related
Rename Embedded Active Chart Sheet Name
For example, a cell on the screen is blank. When the page is printed, the "blank cell" has printed text. I checked "format cells" and it shows word wrap, merge cells, and left to right. I was told to "un-merge" the cells. I did that and I still have the problem. The color on all the cells is OK at a blue shade. When I look at print preview the cells are blank, but when the sheet is printed, the text shows. Also, it looks like there are 2-3 cells inside the main cell. I hope I have explained this properly, it is not easy to use the correct language to describe since I am not very good at using Excel.
View Replies!
View Related
Sheet Names As Embedded Chart Titles
I have a spreadsheet that has multiple sheets and each sheet has a graph, at oresent if i have to add a new sheet and graph i copy the last sheet, rename the tab then off we go, but i alway have to remember to also change the sheets graph title. is a mechanism so that if I change the Sheet Tab name then the chart will use the sheet tab as a chart title.
View Replies!
View Related
Copy Chart To Different Sheet & Change Reference
I am copying the entire contents of a worksheet onto a blank worksheet multiple times. The problem occurs with the charts which need updating once copied to reference the new worksheet name. I have tried stepping through each chart and then each SeriesCollection but it seems to fall over part way through.
View Replies!
View Related
3D Chart Macro
I am trying to use VBA codes to add a 3D Excel Charts. eg. i have x(100), y(100) and z(100). i want to plot x-y-z chart with 100 data on each axis. the data series are not from the worksheet cells, but all created from the codes. May I know how do i put the VBA codes together to generate the chart.
View Replies!
View Related
Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way. So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well. This solution also worked perfectly, but now I've been told to create the graph without macros. Does anyone have any suggestions? The requirements/details are as follows: 1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH" 2. The Pivot Table has two criteria - School Name and Year Level 3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
View Replies!
View Related
Controlling A Chart In A Macro
In Excel 2007, I'm writing a macro to create several charts (column) that need to match what a designer has already built. I'm having a devil of a time finding the code I need to create parts of the chart. I've searched the web and this forum, but I must just not be asking the right things. I can see everything I want to do in the format pop-up window when I right click on the charts in Excel, here is the path: 1) Format Minor Gridlines, Line Style, Dash Type, Rounded Dot 2) Format Axis, Axis Options, Major tick mark type, None 3) Format Axis, Axis Options, Position Axis, Between tick marks
View Replies!
View Related
Chart Scaling With Macro
I have a spreadsheet with a massive amount of data in it. I have this information displayed as a graph in the sheet "Chart2". I want to be able to scroll my chart scale minimum and maximum to either side with buttons "Button1" and "Button2" located on that chart, and to be able to change the width of the graph by increasing or decreasing the chart scale maximum. This may be a bit of a newbie question, but I haven't dealt with charts at all yet.
View Replies!
View Related
Make A Chart By Using Macro
I am trying to make a chart and I just used the macro recording thing to get the code. I have a variable called days. ActiveChart.SeriesCollection(1).XValues = "='Pair Data'!R2C2:R24C2" Instead of going to row 24 I want to go to row days+1 so I did the following: ActiveChart.SeriesCollection(1).XValues = "='Pair Data'!R2C2:R"&days+1&"C2" how I can fix it? It highlights the "C2" at the end and says Expected End of Statement.
View Replies!
View Related
Macro Creating Second Chart
I'm not too familiar with VB, and I'm trying to narrow down why my macro (and how) is creating a second, duplicate chart. I only need the chart to be created (and the corresponding data fields on the 3rd worksheet) once.
View Replies!
View Related
Macro / Combo Box / Chart
How to get a macro that will display a chart based on the values of a combo box? i have a combo box, with list info and a button. what i am aiming for is, if "total A/R" is selected in combo box, when Button is pressed, i want it to display that specific chart. is that something that is possible?
View Replies!
View Related
Chart Macro Multiple Times
I have a problem with multiple charts in one sheet. The problem is really weird because when i add the charts to the sheet where all the values are then there is no problem, but when i set the position of the charts to another sheet and specific position it gives me an error Here is my sub for making the chart: Sub chartFormat(FChart As Chart, Hø As Integer, Bre As Integer) With FChart .ChartType = xlLine With .Parent .Height = Hø .Width = Bre End With With .SeriesCollection(1) ' THIS IS WHERE THE ERROR OCCURS .Border.ColorIndex = 10 .Border.Weight = xlThick End With With .Axes(xlCategory) With .TickLabels .Alignment = xlCenter .ReadingOrder = xlContext .Orientation = xlUpward End With End With are the sheets where I wanna place the chart and where I get the data. Now if I change profVis to sag all places then it works fine and it places all the charts and make them perfect. But when profVis is there, it makes and error 1004 : Method 'SeriesCollection' of object '_Chart' failed. But the error first occurs after the first 2 chart have been made. I've tried switching them, so the last chart becomes the second, and it can make it. It seems as if there is a limit of 2 chart?
View Replies!
View Related
Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open. 2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version? 3) am i going about this the wrong way with the objects? should i be after vba code?
View Replies!
View Related
Drop Down Chart To Run A Graph Macro
I am trying to figure out how to activate a macro from each item in a drop down list. I also need the macro to recognize which item is chosen and to use that items data (say cells C3:C11) to create its graph. I know how to create an individual macro that will create a graph for the item but not a general macro that can be used to graph all items just discerning the difference in where the data is (which item to graph). Is this possible????if so please include code (amatuer programmer).
View Replies!
View Related
Array Limit For A Macro Created Chart
I am working in a macro that will do some calculations and I would like to end it by plotting the results in some charts. Results are in one dimension array form, with probably more than 1000 elements. Can anybody tell me if there is any limit for the arrays that are going to be asigned to the XValues and Values of the chart?
View Replies!
View Related
How To Autorun A Macro Which Reformats A Pivot Chart
I have a pivot chart created that has a stacked bar on one axis and a line on a second access. I have several page items that are needed but when anyone changes the page item the chart reverts back to a single axis bar chart. I wrote a macro that fixes it but I can't figure out how to get the macro to run automatically when a page item is changed. I need to this autorun since I'm emailing the file out. I also have the chart still with the generic Chart 1 name, to make this work better should I name each tab?
View Replies!
View Related
Delete Chart Series Via Macro Code
I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4. (The 0%, 5% 10% air voids and the 95% comp line) iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...
View Replies!
View Related
Place Chart On Specific Range Macro
I'm working on an assignment that pulls information for different countries and I'd like to know how to go about placing the charts on a specific area. Any help would be greatly appreciated! The concept: With each click of a country, the country's information will be pulled from a 'data dump' that I created in the same file. The map and flag are also shown. Some of that information is just using lookups, or a combination of lookups and code, and the hardest part are the GRAPHS - which I'm having a really hard time placing and adjusting accordingly. The problem: As of now the charts are automatically filled as the user clicks a new country, but all of the charts are placed in weird locations, but I'd like for them to be placed in the specified ranges above. I would like charts in the following ranges: A20:B30 (Immigrant Group) D20:E30 (Ethinc Group) F20:G30 (Languages) F13:G19 (Religion) D10:E17 (Age structure) This is the major part of my project that I need to complete. Any help would be greatly appreciated, PLEASE. =) Right now I have ranges that aren't accurate, but regardless of how I try to show my range, the function does not work: Sub CreateCharts() Dim counter As Integer, chartname As String, xvals As String, offset As Integer, Range As String 'Delete all charts Dim oc As ChartObject For Each oc In ActiveWorkbook.ActiveSheet.ChartObjects oc.Delete Next oc
View Replies!
View Related
Chart Title Manipulation With Macro Code
I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements. Sub Chart_Updater() Sheets(Chart1var).Select With ActiveChart .HasTitle = True If Chart1var = "COLI VUL 1 Fund Chart" Then .ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _ & sday & "-" & sday & "-" & Lyear ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then .ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _ & sday & "-" & sday & "-" & Lyear ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then .ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _ The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.
View Replies!
View Related
Running A Chart Macro On Selected Cells And Repeating It
I have figured out how to write a macro to make charts for me automatically, but it only runs the macro on the exact same range every time. I have about 100 different students to make charts for and would really like to find a way to do it more automatically Is there a way to write a Macro so that I can run the same steps on a different range (same number of rows and columns)? Edit: having learned from my first post, I am adding more information. the first chart is from the data in the range from B5-H7 the next chart would be from the data in the range from B11-H13 The charts are not evenly spaced from each other. I will need to select the range (same size) each time. I can record the Macro, I just can't apply it where ever I want to.
View Replies!
View Related
Append Macro To Chart: Resize Plot By Click
I would like to click on my chart, which then gets resized automatically depending on its current size. Situation: My main macro creates various charts, all of them I connect to the macro "chartMakro". After the main macro has finished successfully, I can click on such a chart and the macro "chartMakro" is launched, supposedly resizing the chart. unfortunately i do NOT know how to tell the macro which is the active sheet, therefore my macro fails to work. Question: When a click on a chart launches a macro, how can I tell the macro which chart called it? 'appending a macro to a chart ' MAIN MACRO '[...] activeChart.ChartArea.Select ActiveWindow.Visible = False Selection.OnAction = "chartMakro"............
View Replies!
View Related
Date Based Bar Chart Macro Code
I am working on a manning document which tells me when people report, and depart based on their job position. I have all of the info in one worksheet, and want to generate a bar like graph on another worksheet in the workbook (by just changing the cell color for ease) for a snapshot to view holes in job positions. The snapshot worksheet has the list of jobs on the left, and months/years in a line acorss the top i.e. 2006 | J | F | M | A | M | J | J | A | S | O | N | D | Job Position X X X X X X Job Position X X X X X X Here was the code I started with, and it doesn't do anything! Private Sub Workbook_CreateCalendar(ByVal Sh As Object, ByVal Target As Range) Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim Fcol As Date Dim Lcol As Date Dim Lencol As Long Dim CalcMode As Long Dim ViewMode As Long 'Set the Start Date of your Sheet Dim DatTim1 As Date DatTim1 = #1/1/2006#
View Replies!
View Related
2007 Macro Recorder Not Recording Chart Property Changes
I've created VBA code in Access 2007 to create a column chart in Excel, and it is working. I need to change the rotation on my category labels to 270 degrees vertical. When I record a macro on the chart in Excel 2007. I only receive the following Sub chartingRotation() ' ' chartingRotation Macro ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlCategory).Select ActiveSheet.ChartObjects("Chart 1").Activate End Sub How can I get the macro recorder to record changes to chart properties?
View Replies!
View Related
|
TRACKER
