chart help

**H_Kennedy** · 12-07-2013, 09:25 PM

How do I re-arrange this code please. I need the second chart to appear first and the first chart to appear 2nd and the 3rd chart is correct position for across the sheet

Also I need the first chart to look at range E6:F7,E12:F13 only...this will allow it to appear exactly the same format as 2nd and 3rd chart as they currently appear in Chart sheet

Thank you

**Andy Pope** · 12-08-2013, 08:10 AM

Please Login or Register  to view this content.

**H_Kennedy** · 12-08-2013, 08:43 AM

Hi Andy

Thank you for the excellent help

I have just 1 more request please

How do I remove the series label from each Chart
Can the 1st Chart be positioned more to the centre of the page

Appreciated

**Andy Pope** · 12-09-2013, 04:32 AM

This will remove the legend and increase the starting left position

Please Login or Register  to view this content.

**H_Kennedy** · 12-10-2013, 04:06 AM

Thanks Andy

How do i add a Chart title to each Charts please

Thanks

**Andy Pope** · 12-10-2013, 04:38 AM

add code to output chart title. You don't say what the title should be so this just uses team and chart index values

Please Login or Register  to view this content.

**H_Kennedy** · 12-10-2013, 05:15 AM

Thanks Andy

**H_Kennedy** · 12-10-2013, 05:25 AM

Is the following possible please

When i select a Team from the Userform that it create a new Sheet for each team.If i select 1 team it only creates 1 sheet with the Team 1 Chart.If i select 2 the same and if 3 selected it creates 3 new sheets with a team chart for each Sheet.If Team 1 selected it creates a sheet called Team 1 with Team 1 Charts and same for 2 and 3

Thank you

**Andy Pope** · 12-10-2013, 11:42 AM

Please Login or Register  to view this content.

**H_Kennedy** · 12-11-2013, 04:23 AM

Hi Andy

What part of the code do i change so that i only have 1 Chart on each Sheet for the range E14:E16 ?

Thanks

Please Login or Register  to view this content.

**Andy Pope** · 12-11-2013, 06:25 AM

Please Login or Register  to view this content.

**H_Kennedy** · 12-11-2013, 06:34 AM

Thanks Andy

Would it be possible to create each sheet with the Chart like the attached workbook

Also is there a code which when a Commandbutton is pressed that it then deletes the 3 Chart Sheets

Thanks Again

**Andy Pope** · 12-11-2013, 06:40 AM

If you only want the 3rd chart from the 3 data sets defined just change the lngChartIndex loop

Please Login or Register  to view this content.

I'm sure you can recorder the code requried to delete a sheet.

**H_Kennedy** · 12-11-2013, 07:18 AM

Thanks

I have this code but it only deletes 1 sheet at a time instead of the 3 sheets in 1 go

Please Login or Register  to view this content.

I changed the code below to lngChartIndex = 3 To 3 ... it does give the 3rd chart for each sheet but it does it twice

Please Login or Register  to view this content.

**Andy Pope** · 12-11-2013, 07:33 AM

Ask once and then delete whilst looping

Please Login or Register  to view this content.

You have 2 copies because you have two sets of code. If you only want 1 chart then remove the other set of code.

**H_Kennedy** · 12-11-2013, 08:17 AM

Thanks

The sheets are now correct with 1 copy for each Chart but the code is not deleting the 3 sheets

Thanks

**Andy Pope** · 12-11-2013, 08:24 AM

Like is case sensitive so "team-1" is not like "Team*"

Please Login or Register  to view this content.

**H_Kennedy** · 12-11-2013, 08:32 AM

Thanks Andy

! final request.....can the messagebox say "are you sure you want to delete charts" - take the Team-1 out of the messagebox

**Andy Pope** · 12-11-2013, 08:34 AM

Seriously??

Surely if you created that code you can simply modify it.

Please Login or Register  to view this content.

**H_Kennedy** · 12-12-2013, 02:33 AM

Hi Andy
Thanks for the help
Yes you are correct but really struggling having done 3 x 16 hour days and possibly again today i am struggling.
I as you can see do not know much at all with Chart VBA.What do you recommend as a good source to learn?

I added an extra column to the charts.It picks the highest percentage from the data and displays on the Y-Axis....can the code be altered so that there is always 100 showing on the Y-Axis....increments of 10 starting at 0 and finishing at 100?
The percentages will always then be shown against 100
Can the date from the userform be placed underneath the percentage headings at the bottom of Chart?

I am hoping this will finish this

Appreciated

**Andy Pope** · 12-12-2013, 04:32 AM

This will set value axis and add date text to category axis title

Please Login or Register  to view this content.

Learning wise, macro recorder, object browser.
Books on charting, including vba, Excel 2007 Charts (Mr. Spreadsheet's Bookshelf) by John Walkenbach.
Some coding examples here
http://peltiertech.com/Excel/Charts/chartvba.html

**H_Kennedy** · 12-14-2013, 05:42 AM

Thanks Andy
Thanks for the sites

I used this code below for another workbook I have. I changed the sheet name from Data to Main. Also I am using CHART instead of Team in the userform.Now this works fine the code in the new workbook but would you recommend I change the Private Function code or any of the Dim in the code as these were originally referring to Team but has since been changed to CHART.I also changed the code to .ChartTitle.Text = "CHART " & Chr(lng + 65) so as I have a letter instead of a number.

The code below though does work fine in the new workbook

Please Login or Register  to view this content.

**Andy Pope** · 12-14-2013, 06:17 AM

You can change the function name to be more generic.

Remove the unsed variable lngChartIndex. and replace lngTeamIndex with lngDataIndex

Please Login or Register  to view this content.

**H_Kennedy** · 12-15-2013, 04:37 AM

Thanks Andy

Is it possible to add a Pie chart Option to the Userform.A dropdown list to select either Pie or Column.I have created userforms for other workbooks but not sure how to add it here for charts.I know how to add the Combobox to the userform itself though

I used the macro recorder for the following code for a Pie Chart although it will use the ranges already in the code for the column charts

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Andy Pope** · 12-15-2013, 08:11 AM

This uses a couple of option buttons to choose chart type.

**H_Kennedy** · 12-15-2013, 09:13 AM

Thanks Andy

I`m just about done. Finally can the Labels (numbers) be displayed on the outside of the Pie Chart including % instead of being inside each section of the Pie Chart

Thanks for all your help

**Andy Pope** · 12-16-2013, 04:33 AM

extend the code.

Please Login or Register  to view this content.

**H_Kennedy** · 12-16-2013, 05:53 AM

Thanks Andy

I placed in the code and it worked although it did not display a % in the Pie Chart.Also it gave an error for the Column Chart so when I changed the .ShowPercentage = True to False it worked without error but still no % in Pie Chart.I suspect though I made some placement of the code causing the error

Please Login or Register  to view this content.

**Andy Pope** · 12-16-2013, 06:00 AM

Please Login or Register  to view this content.

**H_Kennedy** · 12-16-2013, 06:27 AM

Thanks Andy

**H_Kennedy** · 12-16-2013, 09:08 AM

Hi Andy

I have had to slightly redesign the data base. I now have the Chart sheets permantly in the workbook.I have started the userform for Group A (I will create the rest).Can your code fit with the userform for each sheet.I do not need to create a sheet for each chart as they will remain there permantly.I have code for transferring data for the rest of the sheet but am still not real sure with Charts yet.

I used a listbox in the userform to designate Group A....is this correct?

Thanks

**Andy Pope** · 12-17-2013, 05:12 AM

You only need 1 userform, which can be used for all groups.

The userform has some public variables that can be set for each group before using the same routine with arguments to generate the chart

**H_Kennedy** · 12-17-2013, 05:41 AM

Thanks Andy

I learnt more about userforms as well..thanks

I have been using a button to delete the Charts.When selected it deletes the whole Chart....not sure about this but can the Charts Columns be deleted only leaving everything else in the sheet from the Chart....in the attached workbook can a button when selected to delete the Chart that it only remove the Columns and leave the rest of the Chart on the sheet.When i then go to place a new Chart all it needs to do is add Columns to the Chart frame already in the worksheets

Thanks

**Andy Pope** · 12-17-2013, 06:04 AM

You need to set the series to have empty data values.

You also need to alter the createchart routine to not add chart or series to exisiting chart

**H_Kennedy** · 12-17-2013, 06:09 AM

Hi Andy

Excellent..thank you

**H_Kennedy** · 12-17-2013, 08:05 AM

Hi Andy

Curious about this code line

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Andy Pope** · 12-17-2013, 08:36 AM

In what way?

It sets the values for the series to empty range thus giving the chart it's empty appearence.

**H_Kennedy** · 12-17-2013, 08:39 AM

Just the range - ("MAIN").Range("C14:C17")

I used this for another workbook too. I changed the range and it does place the Chart except for the Columns...I am trying to workout where in the code I may have gone wrong

**Andy Pope** · 12-17-2013, 08:41 AM

That range is to remove column height from chart.

**H_Kennedy** · 12-17-2013, 08:46 AM

ok thank you

**H_Kennedy** · 12-17-2013, 09:18 AM

The date was out by 1 day so I changed the following.It works but I want to make sure I made the correct change

Very happy with this outcome so finally can the Process time column be in RED and the 3 other columns be the color Blue.....I used ChartColor = 22 but it errorred

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**Andy Pope** · 12-17-2013, 12:10 PM

Please Login or Register  to view this content.

**H_Kennedy** · 12-18-2013, 02:52 AM

Thanks

It works only if i remove protection...i have this WorkBook code

Please Login or Register  to view this content.

I use this in my other sheets but i am not getting it to work here

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I changed the colors of all 4 columns using the following.It works so i guess it is correct

Please Login or Register  to view this content.

**Andy Pope** · 12-18-2013, 04:45 AM

as you only post 1 line of code it's hard to tell what 'work here' means.

You need to unprotect the sheet before creating or clearing the chart.

**H_Kennedy** · 12-18-2013, 05:04 AM

Hi

If I use code can it allow me to use the chart sheet without having to protect/unprotect

Here is one of the Userform codes which has protection . All 5 Userforms use Protection...it allows me to use the workbook without having to unprotect/protect sheets all the time

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Andy Pope** · 12-18-2013, 05:10 AM

If the UserInterfaceOnly has been applied then you should via code be able to write to sheet.

But this code changes that by removing the protection and then applying without the UserInterfaceOnly.

Please Login or Register  to view this content.

so try it without the Protect/Unprotect. If that fails include UserInterFaceonly argument.

**H_Kennedy** · 12-19-2013, 02:31 AM

Thanks Andy

I will give it a try and let you know

Thanks

**H_Kennedy** · 12-19-2013, 05:27 AM

Hi

I removed all protection and now the Charts work fine.The only issue I have is with the date.To get the data from the 17/12/2013 on to the Charts I now have to select 18/12/2013.The code is -3 against todays date but it is only going back 2 days....worked fine before so not sure as I have played with the date code but still not correct

Please Login or Register  to view this content.

**Andy Pope** · 12-19-2013, 05:59 AM

If only the information in the combobox is rolling then you will need to include an offset value so the correct column in your static data is used.

Or write a function to locate the correct column of data to use based on the date selected.

Why are you excluding older dates for charting?

**Andy Pope** · 12-19-2013, 06:26 AM

this modification will locate matching date.

Please Login or Register  to view this content.

**H_Kennedy** · 12-19-2013, 06:57 AM

Hi Andy

That works just great.Thank you for all your help

**H_Kennedy** · 12-21-2013, 05:12 AM

Hi Andy

Instead of having 3 sheets ( 1 for each Chart group) can there be 1 sheet called Charts which will have the Column Chart area (same as in each sheet) and with a Userform I select either Group A,Group B or Group C and it puts columns in the chart the columns according to which group....CAn all code for the Pie Chart be removed and only use a Column Chart...when selecting a Group it will only need to change the Chart Title to suit the Group chosen as they will all use the same axis titles....I will also remove the Group A,B & C sheets if this is possible.....will mark as solved

Thanks

**Andy Pope** · 12-21-2013, 07:02 AM

see attached

**H_Kennedy** · 12-21-2013, 07:24 AM

Appreciated and thanks for all your help

**H_Kennedy** · 01-01-2014, 01:09 AM

I have changed the Team 2 and Team 3 from being underneath Team 1 in sheet 'INFO' to going across the sheet....I was able to change the offset to suit when going down the sheet but how do I change the code for the Chart to read across the sheet....the Chart works for Team 1 but not to Team 2 and Team 3....

**Andy Pope** · 01-01-2014, 09:08 AM

Please Login or Register  to view this content.

**H_Kennedy** · 01-01-2014, 09:12 AM

Of course .. thanks Andy

chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Re: chart help

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Chart needs to auto populate items from master referance chart based on job description.

Bookmarks

Bookmarks

Posting Permissions