Changing Series for a chart

[drew1987]

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?

 

[Jon Peltier]

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

 

[drew1987]

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)

 

[drew1987]

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)

 

[Ed Ferrero]

Sounds like you are trying to do too much in VBA. Look at the 'Reporting'
example at http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx

This does not use VBA, just demonstrates good layout and the use of OFFSET
functions.

 

[Jon Peltier]

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

 

[drew1987]

This is exactly what I was looking for! I need to do more with this now!

Thanks alot Jon!