Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro that will add data from multiple workbooks to the 1st open r | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
How do I record a macro which should work on multiple files ? | Excel Discussion (Misc queries) | |||
Zip Code Macro | Excel Worksheet Functions | |||
Macro for multiple charts | Excel Worksheet Functions |