Macro as multiple use code
 

I was given a spreadsheet with the following columns:
a button column (to run a macro)
a persons name
6 monthly columns of figures

When the button is clicked the macro associated is run and a cute little
chart for the row pops up. The first 3 rows have the buttons and the
associated macros. My job, is to populate the macro for the rest of the
1780 rows.
The code for the current Macros :
Sub Button1()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C3:R3C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R3C2"
End Sub
Sub Button2()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C3:R4C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R4C2"
End Sub
Sub Button3()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R5C3:R5C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R5C2"
End Sub

Maybe it's because I am a programer, but I have to believe there is an
easier way of doing this. One sub that will, based upon the row of the
button clicked, run for every row and create the chart.
I have been searching microsoft and the rest of the web with no luck.
Might be able to find something if I had a good grasp of the terminology.

Thank You,
Bruce

Bruce,

Sub ButtonAll()
Dim myRange As Range
Set myRange = ActiveCell
'or use
'Set myRange = ActiveSheet.Cells(CInt(InputBox("Enter row Number")), 3)

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R" & _
myRange.Row & "C3:R" & myRange.Row & "C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R" & _
myRange.Row & "C2"
End Sub

HTH,
Bernie
MS Excel MVP


"Bruce Neylon" wrote in message
link.net...
I was given a spreadsheet with the following columns:
a button column (to run a macro)
a persons name
6 monthly columns of figures

When the button is clicked the macro associated is run and a cute little chart for the row pops
up. The first 3 rows have the buttons and the associated macros. My job, is to populate the
macro for the rest of the 1780 rows.
The code for the current Macros :
Sub Button1()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C3:R3C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R3C2"
End Sub
Sub Button2()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C3:R4C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R4C2"
End Sub
Sub Button3()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R5C3:R5C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R5C2"
End Sub

Maybe it's because I am a programer, but I have to believe there is an easier way of doing this.
One sub that will, based upon the row of the button clicked, run for every row and create the
chart.
I have been searching microsoft and the rest of the web with no luck. Might be able to find
something if I had a good grasp of the terminology.

Thank You,
Bruce

Bruce,


The key point that I missed sending with my first post (hit send too soon) was to put the button on
row 1 and then freeze the row to always show it. Then scroll down, select a cell in the row of
interest, and then press the button.

Sub ButtonAll()
Dim myRange As Range
Set myRange = ActiveCell
'or use
'Set myRange = ActiveSheet.Cells(CInt(InputBox("Enter row Number")), 3)

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R" & _
myRange.Row & "C3:R" & myRange.Row & "C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R" & _
myRange.Row & "C2"
End Sub


--
HTH,
Bernie
MS Excel MVP


"Bruce Neylon" wrote in message
link.net...
I was given a spreadsheet with the following columns:
a button column (to run a macro)
a persons name
6 monthly columns of figures

When the button is clicked the macro associated is run and a cute little chart for the row pops
up. The first 3 rows have the buttons and the associated macros. My job, is to populate the
macro for the rest of the 1780 rows.
The code for the current Macros :
Sub Button1()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C3:R3C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R3C2"
End Sub
Sub Button2()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C3:R4C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R4C2"
End Sub
Sub Button3()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R5C3:R5C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R5C2"
End Sub

Maybe it's because I am a programer, but I have to believe there is an easier way of doing this.
One sub that will, based upon the row of the button clicked, run for every row and create the
chart.
I have been searching microsoft and the rest of the web with no luck. Might be able to find
something if I had a good grasp of the terminology.

Thank You,
Bruce

I can live with this. :-) Thank you very much.

One step further, is there a way to make clicking the button activate
the row? Or is the button, not really part of the row?

Again, Thanks,
Bruce

Bruce,

If you have one button and a frozen first row, then the button isn't really part of the rows of
data, just part of the top row. Do you want to use the inputbox version, and then have Excel scroll
to that row? Or do you want to select a single cell in the row (manually scrolling to it) and then
have Excel select the data from the row? (I'm not really sure what you want when you say 'activate
the row'. What row?)

HTH,
Bernie
MS Excel MVP


"Bruce Neylon" wrote in message
k.net...
I can live with this. :-) Thank you very much.

One step further, is there a way to make clicking the button activate the row? Or is the button,
not really part of the row?

Again, Thanks,
Bruce

Bernie,

Oh, I see where you are coming from.

I'll explain. The guy that originally set the thing up has column "A"
as a column of buttons, in A3 through A1787. I was asking, if I click
on the button in A1500 without first clicking on b1500, etc., can I know
which button I clicked and have the chart for row 1500 display?

I like the idea of the single button to push, I might try to get them to
go along with that.

Oh, and when I said "activate" I meant what happens when you click on
the a row and then click the button. The row is selected. Hmmm, was it
Churchill who said something about our common language separating us? :-D

Thanks,
Bruce


Bernie Deitrick wrote:

Bruce,

If you have one button and a frozen first row, then the button isn't really part of the rows of
data, just part of the top row. Do you want to use the inputbox version, and then have Excel scroll
to that row? Or do you want to select a single cell in the row (manually scrolling to it) and then
have Excel select the data from the row? (I'm not really sure what you want when you say 'activate
the row'. What row?)

HTH,
Bernie
MS Excel MVP

Hundreds of buttons is a nightmare waiting to happen - file corruption is much more common with that
many objects.

You could always add:

Activesheet.Activate
Cells(Activecell.Row,2).Resize(1,7).Select

HTH,
Bernie
MS Excel MVP


"Bruce Neylon" wrote in message
k.net...
Bernie,

Oh, I see where you are coming from.

I'll explain. The guy that originally set the thing up has column "A" as a column of buttons, in
A3 through A1787. I was asking, if I click on the button in A1500 without first clicking on
b1500, etc., can I know which button I clicked and have the chart for row 1500 display?

I like the idea of the single button to push, I might try to get them to go along with that.

Oh, and when I said "activate" I meant what happens when you click on the a row and then click the
button. The row is selected. Hmmm, was it Churchill who said something about our common language
separating us? :-D

Thanks,
Bruce


Bernie Deitrick wrote:

Bruce,

If you have one button and a frozen first row, then the button isn't really part of the rows of
data, just part of the top row. Do you want to use the inputbox version, and then have Excel
scroll to that row? Or do you want to select a single cell in the row (manually scrolling to it)
and then have Excel select the data from the row? (I'm not really sure what you want when you
say 'activate the row'. What row?)

HTH,
Bernie
MS Excel MVP