Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
I am making my own budget spreadsheet and have a ComboBox with all 12 months
and a chart showing the Income, Expense, and Balance of a given month. My problem is I can't code VB to automatically change the series values for the chart. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
How is the data arranges? What kind of chart is it and how do you want it to
reflect what the combo shows? This tutorial might have something you can use: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drew1987" wrote in message ... I am making my own budget spreadsheet and have a ComboBox with all 12 months and a chart showing the Income, Expense, and Balance of a given month. My problem is I can't code VB to automatically change the series values for the chart. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
I have 2 worksheets, "Budget" and "Chart".
Budget (Categories in no particular order): row/column D E O Jan Feb ......... Dec 6 Income 200 200 200 ..... 12 Balance 100 100 100 13 Expenses 100 100 100 Chart: I have a Column Chart of all 12 months, but I need (per selection of ComboBox) 1. Monthly Regular Bar chart of inc, exp, and balance 2. Pie Chart of subsections of Expenses (I can probably get this after figuring out #1) "Jon Peltier" wrote: How is the data arranges? What kind of chart is it and how do you want it to reflect what the combo shows? This tutorial might have something you can use: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drew1987" wrote in message ... I am making my own budget spreadsheet and have a ComboBox with all 12 months and a chart showing the Income, Expense, and Balance of a given month. My problem is I can't code VB to automatically change the series values for the chart. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
That didn't come out right, but its setup like your example. I couldn't view
your code, so here is mine to get the value for the OFFSET. Private Sub MonthList_Change() If MonthList.Value = "" Then Exit Sub ElseIf MonthList.Value = "January" Then Range("B35").Value = 1 ElseIf MonthList.Value = "February" Then Range("B35").Value = 2 ElseIf MonthList.Value = "March" Then Range("B35").Value = 3 ElseIf MonthList.Value = "April" Then Range("B35").Value = 4 ElseIf MonthList.Value = "May" Then Range("B35").Value = 5 ElseIf MonthList.Value = "June" Then Range("B35").Value = 6 ElseIf MonthList.Value = "July" Then Range("B35").Value = 7 ElseIf MonthList.Value = "August" Then Range("B35").Value = 8 ElseIf MonthList.Value = "September" Then Range("B35").Value = 9 ElseIf MonthList.Value = "October" Then Range("B35").Value = 10 ElseIf MonthList.Value = "November" Then Range("B35").Value = 11 ElseIf MonthList.Value = "December" Then Range("B35").Value = 12 End If End Sub Then to get each months inc/exp/bal I put O36, O37, O38 respectively. I cant get this part to work yet because of the problem above. =offset(B36, 0, $B$35) =offset(B37, 0, $B$35) -offset(B38, 0, $B$35) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
Sounds like you are trying to do too much in VBA. Look at the 'Reporting'
example at http://www.edferrero.com/ExcelCharts...2/Default.aspx This does not use VBA, just demonstrates good layout and the use of OFFSET functions. -- Ed Ferrero www.edferrero.com "drew1987" wrote: That didn't come out right, but its setup like your example. I couldn't view your code, so here is mine to get the value for the OFFSET. Private Sub MonthList_Change() If MonthList.Value = "" Then Exit Sub ElseIf MonthList.Value = "January" Then Range("B35").Value = 1 ElseIf MonthList.Value = "February" Then Range("B35").Value = 2 ElseIf MonthList.Value = "March" Then Range("B35").Value = 3 ElseIf MonthList.Value = "April" Then Range("B35").Value = 4 ElseIf MonthList.Value = "May" Then Range("B35").Value = 5 ElseIf MonthList.Value = "June" Then Range("B35").Value = 6 ElseIf MonthList.Value = "July" Then Range("B35").Value = 7 ElseIf MonthList.Value = "August" Then Range("B35").Value = 8 ElseIf MonthList.Value = "September" Then Range("B35").Value = 9 ElseIf MonthList.Value = "October" Then Range("B35").Value = 10 ElseIf MonthList.Value = "November" Then Range("B35").Value = 11 ElseIf MonthList.Value = "December" Then Range("B35").Value = 12 End If End Sub Then to get each months inc/exp/bal I put O36, O37, O38 respectively. I cant get this part to work yet because of the problem above. =offset(B36, 0, $B$35) =offset(B37, 0, $B$35) -offset(B38, 0, $B$35) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
Use a forms toolbar listbox, populate it with the list of months, and use
B35 as the link cell. B35 will then contain values between 1 and 12, based on what has been selected. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drew1987" wrote in message ... That didn't come out right, but its setup like your example. I couldn't view your code, so here is mine to get the value for the OFFSET. Private Sub MonthList_Change() If MonthList.Value = "" Then Exit Sub ElseIf MonthList.Value = "January" Then Range("B35").Value = 1 ElseIf MonthList.Value = "February" Then Range("B35").Value = 2 ElseIf MonthList.Value = "March" Then Range("B35").Value = 3 ElseIf MonthList.Value = "April" Then Range("B35").Value = 4 ElseIf MonthList.Value = "May" Then Range("B35").Value = 5 ElseIf MonthList.Value = "June" Then Range("B35").Value = 6 ElseIf MonthList.Value = "July" Then Range("B35").Value = 7 ElseIf MonthList.Value = "August" Then Range("B35").Value = 8 ElseIf MonthList.Value = "September" Then Range("B35").Value = 9 ElseIf MonthList.Value = "October" Then Range("B35").Value = 10 ElseIf MonthList.Value = "November" Then Range("B35").Value = 11 ElseIf MonthList.Value = "December" Then Range("B35").Value = 12 End If End Sub Then to get each months inc/exp/bal I put O36, O37, O38 respectively. I cant get this part to work yet because of the problem above. =offset(B36, 0, $B$35) =offset(B37, 0, $B$35) -offset(B38, 0, $B$35) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Series for a chart
This is exactly what I was looking for! I need to do more with this now!
Thanks alot Jon! "Jon Peltier" wrote: Use a forms toolbar listbox, populate it with the list of months, and use B35 as the link cell. B35 will then contain values between 1 and 12, based on what has been selected. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drew1987" wrote in message ... That didn't come out right, but its setup like your example. I couldn't view your code, so here is mine to get the value for the OFFSET. Private Sub MonthList_Change() If MonthList.Value = "" Then Exit Sub ElseIf MonthList.Value = "January" Then Range("B35").Value = 1 ElseIf MonthList.Value = "February" Then Range("B35").Value = 2 ElseIf MonthList.Value = "March" Then Range("B35").Value = 3 ElseIf MonthList.Value = "April" Then Range("B35").Value = 4 ElseIf MonthList.Value = "May" Then Range("B35").Value = 5 ElseIf MonthList.Value = "June" Then Range("B35").Value = 6 ElseIf MonthList.Value = "July" Then Range("B35").Value = 7 ElseIf MonthList.Value = "August" Then Range("B35").Value = 8 ElseIf MonthList.Value = "September" Then Range("B35").Value = 9 ElseIf MonthList.Value = "October" Then Range("B35").Value = 10 ElseIf MonthList.Value = "November" Then Range("B35").Value = 11 ElseIf MonthList.Value = "December" Then Range("B35").Value = 12 End If End Sub Then to get each months inc/exp/bal I put O36, O37, O38 respectively. I cant get this part to work yet because of the problem above. =offset(B36, 0, $B$35) =offset(B37, 0, $B$35) -offset(B38, 0, $B$35) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Values in Chart Series | Charts and Charting in Excel | |||
Pie chart-remove series w/out changing percent in pie | Charts and Charting in Excel | |||
Changing chart types with series | Charts and Charting in Excel | |||
Chart select, yet changing set of series | Charts and Charting in Excel | |||
Changing Chart Series Names | Excel Programming |