Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bruce Neylon
 
Posts: n/a
Default 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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Bruce Neylon
 
Posts: n/a
Default

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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Bruce Neylon
 
Posts: n/a
Default

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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro that will add data from multiple workbooks to the 1st open r jbsand1001 Excel Discussion (Misc queries) 0 April 23rd 05 07:52 PM
Make Change Case in Excel a format rather than formula Kevin Excel Worksheet Functions 1 March 18th 05 09:53 PM
How do I record a macro which should work on multiple files ? Venkataraman.P.E Excel Discussion (Misc queries) 2 January 16th 05 11:26 AM
Zip Code Macro Ken Wright Excel Worksheet Functions 0 December 9th 04 08:55 AM
Macro for multiple charts JS Excel Worksheet Functions 1 November 19th 04 04:44 AM


All times are GMT +1. The time now is 02:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"