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?

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" <(E-Mail Removed)> wrote in message
news:774C07A8-BC73-4393-9539-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
> news:774C07A8-BC73-4393-9539-(E-Mail Removed)...
> >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?
>
>
>

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)

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)
>
>
>

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" <(E-Mail Removed)> wrote in message
news:7BFEFE4E-0DDF-4FE0-81DC-(E-Mail Removed)...
> 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)
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:7BFEFE4E-0DDF-4FE0-81DC-(E-Mail Removed)...
> > 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)
> >
> >
> >
>
>
>