Macro as multiple use code

[Bruce Neylon]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Bruce Neylon]

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 Deitrick]

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]

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]

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