macro to expand data range for chart

[Scott Bradley]

I didn't know I was a "newbie" until I tried to do something other than
crunch numbers with Excel. (Using Excel X on Panther)

I have a list of data I want to display in a line graph.
I have defined the graph in a separate sheet, it looks nice.
I have a form for adding a row to the list, it works nice.

I have a macro assigned to the graph such that when you click anywhere
on the graph, the form appears, accepts new data, and returns to the
graph. It looks and works nice with one (serious) problem.

The problem is that the graph range is not updated to include the new
row(s) created through the form (appended to the list).

How do I modify my macro to increase the range to include the new rows?
Or is there a way to define the range such that it includes all of the
data in a row automatically.

 

[J.E. McGimpsey]

I didn't know I was a "newbie" until I tried to do something other than
crunch numbers with Excel. (Using Excel X on Panther)

I have a list of data I want to display in a line graph.
I have defined the graph in a separate sheet, it looks nice.
I have a form for adding a row to the list, it works nice.

I have a macro assigned to the graph such that when you click anywhere
on the graph, the form appears, accepts new data, and returns to the
graph. It looks and works nice with one (serious) problem.

The problem is that the graph range is not updated to include the new
row(s) created through the form (appended to the list).

How do I modify my macro to increase the range to include the new rows?
Or is there a way to define the range such that it includes all of the
data in a row automatically.

I'd use a dynamic range. See Jon Peltier's excellent example at:

http://www.geocities.com/jonpeltier/Excel/Charts/DynamicCharts.html

Post back if you have questions.

 

[Scott Bradley]

I'd use a dynamic range. See Jon Peltier's excellent example at:

http://www.geocities.com/jonpeltier/Excel/Charts/DynamicCharts.html

Post back if you have questions.

See follow-up question at the end.

Before I read your reference, I played around with recording a macro and
snipping pieces I liked and came up with the following solution. Macro
is attached to chart, so user clicks any where on it and the form pops
up to accept new data, and the graph range is expanded to include it
using End(xlDown)

Sub Graph()
'
' Weight Graph Macro
' 11/9/2003 by Scott Bradley
'
Sheets("Data").Select
Range("A2").Select
ActiveSheet.ShowDataForm
ActiveSheet.Shapes("Chart 2").Select
ActiveSheet.ChartObjects("Chart 2").Activate

' expand range of graph

ActiveChart.SetSourceData Source:=Sheets("Data").Range("C1",
Range("A1").End(xlDown)), _
PlotBy:=xlColumns

' sort for easy search next time

Range("A1").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub



QUESTION. This macro works fine on Excel X (Mac) and Excel 2003 (PC).
But if I try to resize the window to exactly fit the chart, the code
generated by record is different on a Mac than on a PC, and neither code
runs on the other. Is there an easy way, like a global or function, to
check which platform I'm running on so I can run the appropriate code?

 

[Bernard REY]

Scott Bradley wrote :

QUESTION. This macro works fine on Excel X (Mac) and Excel 2003 (PC).
But if I try to resize the window to exactly fit the chart, the code
generated by record is different on a Mac than on a PC, and neither code
runs on the other. Is there an easy way, like a global or function, to
check which platform I'm running on so I can run the appropriate code?

One suggestion fot this:

If Application.PathSeparator = ":" Then
System = "Mac"
Else
System = "Win"
End If

What are the differences in the code? There might bo some other Workaround,
as recorded code often has to be "cleaned up" a bit.

 

[J.E. McGimpsey]

QUESTION. This macro works fine on Excel X (Mac) and Excel 2003 (PC).
But if I try to resize the window to exactly fit the chart, the code
generated by record is different on a Mac than on a PC, and neither code
runs on the other. Is there an easy way, like a global or function, to
check which platform I'm running on so I can run the appropriate code?

Take a look at "Strategies for Developing Cross-Platform Solutions"
in VBA Help.