If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I add new chart series automatically in Excel
I may have several dozen or more (even hundreds or thousands) series to add
to a microsoft Excel x-y scatter chart. Each series only consists of two data points and represent a straight line connected to pre-defined nodes in a complex 3-D geometric figure. When plotted, the geometric 3-D figure is revealed, and can be rotated to different viewing angles. Even though Excel Chart is not specifically intended for this type display, it works very well. The only problem is automating the new series as the coordinates for each new line are input. Presently I have to do add each new series individually - or pre-define the series with dummy data. However, doing hundreds or thousands of lines this way is daunting. |
#2
|
|||
|
|||
Hi, I have not understood your task very well. Anyway, I'm trying to answer it. Did you try a named range (say, DATA) for series, like DATA = (OFFSET...)...? Is it what you want??? If yes - give me your mail and I'll send you a file -- turbodance ------------------------------------------------------------------------ turbodance's Profile: http://www.excelforum.com/member.php...o&userid=14911 View this thread: http://www.excelforum.com/showthread...hreadid=265402 |
#3
|
|||
|
|||
Yes, I use named ranges to input some of the data (the node coordinates) as a
single series (no connecting lines), and then an add-in labeler to for their label assignments. But each line segment must be an independent series as its assignment to particular nodes is arbitrary. Jon Peltier also has a VBA example on how to input a group of series all at once. But what I would like to do is add each new line series to the chart as it was defined. This is because input error is more easily discovered and corrected this way. "turbodance" wrote: Hi, I have not understood your task very well. Anyway, I'm trying to answer it. Did you try a named range (say, DATA) for series, like DATA = (OFFSET...)...? Is it what you want??? If yes - give me your mail and I'll send you a file -- turbodance ------------------------------------------------------------------------ turbodance's Profile: http://www.excelforum.com/member.php...o&userid=14911 View this thread: http://www.excelforum.com/showthread...hreadid=265402 |
#4
|
|||
|
|||
I think the way I'd approach this is to have four columns, X1, X2, Y1, Y2. The user
enters the next row of data, and presses a button to add the next series with these values. Instead of a button, you could use the Worksheet_Change event to detect when a new row is populated and add a new series. If these points need labels, then you could have two more columns, Name1, Name2. The event procedure would add these labels to the points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CymonM wrote: Yes, I use named ranges to input some of the data (the node coordinates) as a single series (no connecting lines), and then an add-in labeler to for their label assignments. But each line segment must be an independent series as its assignment to particular nodes is arbitrary. Jon Peltier also has a VBA example on how to input a group of series all at once. But what I would like to do is add each new line series to the chart as it was defined. This is because input error is more easily discovered and corrected this way. "turbodance" wrote: Hi, I have not understood your task very well. Anyway, I'm trying to answer it. Did you try a named range (say, DATA) for series, like DATA = (OFFSET...)...? Is it what you want??? If yes - give me your mail and I'll send you a file -- turbodance ------------------------------------------------------------------------ turbodance's Profile: http://www.excelforum.com/member.php...o&userid=14911 View this thread: http://www.excelforum.com/showthread...hreadid=265402 |
#5
|
|||
|
|||
Jon,
I was hoping you would respond. I have visited your site and find it extensive and most helpful. I have the input data (x1,x2, and y1,y2) in columns already, as well as the label for the line, so all the input data for a series in on the same row. Where I falter is in generalizing the recorded macro so it is not specific to the recorded row. I can create a macro by recording my keystrokes, but then my mind goes blank, and I can't seem to then make it general. It seems I would need someway for the macro to use the current row number, and I'm not sure how to proceed from there in changing the specific absolute cell locations to one where the cell reference is a set column, but a variable or current row . I am new to the Visual Basic Code and although you and others have many excellant examples, I am still struggling to generalize the syntax. Any specific coding would be greatly appeciated. Regards, Cymonm "Jon Peltier" wrote: I think the way I'd approach this is to have four columns, X1, X2, Y1, Y2. The user enters the next row of data, and presses a button to add the next series with these values. Instead of a button, you could use the Worksheet_Change event to detect when a new row is populated and add a new series. If these points need labels, then you could have two more columns, Name1, Name2. The event procedure would add these labels to the points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CymonM wrote: Yes, I use named ranges to input some of the data (the node coordinates) as a single series (no connecting lines), and then an add-in labeler to for their label assignments. But each line segment must be an independent series as its assignment to particular nodes is arbitrary. Jon Peltier also has a VBA example on how to input a group of series all at once. But what I would like to do is add each new line series to the chart as it was defined. This is because input error is more easily discovered and corrected this way. "turbodance" wrote: Hi, I have not understood your task very well. Anyway, I'm trying to answer it. Did you try a named range (say, DATA) for series, like DATA = (OFFSET...)...? Is it what you want??? If yes - give me your mail and I'll send you a file -- turbodance ------------------------------------------------------------------------ turbodance's Profile: http://www.excelforum.com/member.php...o&userid=14911 View this thread: http://www.excelforum.com/showthread...hreadid=265402 |
#6
|
|||
|
|||
Write a procedure to take a row of data and process it into the chart. Don't specify
a row number, but use a variable lRow to signify the row number. Finally, insert this variable as an argument to the procedu Sub AddRowToChart(lRow As Long) '... ' your code goes here '... End Sub Now switch to the sheet that contains the data. Right click on the sheet tab, and select View Code. This opens a special code module that is attached specifically to the sheet. There are two pull downs at the top of the module. In the left one, select Worksheet. This puts the frame of an event procedure in the module, for Worksheet_SelectionChange. We don't want this one, so you can delete it or ignore it. In the right dropdown, select Change. Now you get the event procedure that we need: Private Sub Worksheet_Change(ByVal Target As Range) End Sub What this is, is a procedure that will run every time the sheet changes (almost every time, but definitely every time a user enters a new value). When it runs, the procedure is passed Target, the range that experienced the change. Suppose your five columns of data are in columns A:E. You want to detect a change in these columns, and send the row of the change to the first procedure. You also might not want to add a row to the sheet if it's already been added. For this purpose, I'll use the easy approach of putting a 1 into column F. So we will not process a row that has 1 in column F. If you construct your added series and labels appropriately, they'll update without needing the event procedure. Pseudo code for the event procedu bail out if changed cell isn't in A:E bail out if column F in the row of the changed cell contains 1 bail out if A don't all have data (too soon to process) Here's a cut at the procedure (watch for newsreader line wraps): Private Sub Worksheet_Change(ByVal Target As Range) 'bail out if changed cell isn't in A:E If Not Intersect(Target, Range("A:E")) Is Nothing Then 'bail out if column F in the row of the changed cell contains 1 If Cells(Target.Row, "F") 1 Then 'bail out if A don't all have data (too soon to process) If Application.CountA(Cells(Target.Row, "A").Resize(1, 5)) = 5 Then AddRowToChart Target.Row Cells(Target.Row, "F").Value = 1 End If End If End If End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CymonM wrote: Jon, I was hoping you would respond. I have visited your site and find it extensive and most helpful. I have the input data (x1,x2, and y1,y2) in columns already, as well as the label for the line, so all the input data for a series in on the same row. Where I falter is in generalizing the recorded macro so it is not specific to the recorded row. I can create a macro by recording my keystrokes, but then my mind goes blank, and I can't seem to then make it general. It seems I would need someway for the macro to use the current row number, and I'm not sure how to proceed from there in changing the specific absolute cell locations to one where the cell reference is a set column, but a variable or current row . I am new to the Visual Basic Code and although you and others have many excellant examples, I am still struggling to generalize the syntax. Any specific coding would be greatly appeciated. Regards, Cymonm "Jon Peltier" wrote: I think the way I'd approach this is to have four columns, X1, X2, Y1, Y2. The user enters the next row of data, and presses a button to add the next series with these values. Instead of a button, you could use the Worksheet_Change event to detect when a new row is populated and add a new series. If these points need labels, then you could have two more columns, Name1, Name2. The event procedure would add these labels to the points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CymonM wrote: Yes, I use named ranges to input some of the data (the node coordinates) as a single series (no connecting lines), and then an add-in labeler to for their label assignments. But each line segment must be an independent series as its assignment to particular nodes is arbitrary. Jon Peltier also has a VBA example on how to input a group of series all at once. But what I would like to do is add each new line series to the chart as it was defined. This is because input error is more easily discovered and corrected this way. "turbodance" wrote: Hi, I have not understood your task very well. Anyway, I'm trying to answer it. Did you try a named range (say, DATA) for series, like DATA = (OFFSET...)...? Is it what you want??? If yes - give me your mail and I'll send you a file -- turbodance ------------------------------------------------------------------------ turbodance's Profile: http://www.excelforum.com/member.php...o&userid=14911 View this thread: http://www.excelforum.com/showthread...hreadid=265402 |
#7
|
|||
|
|||
Jon,
Wow, that's a lot of code. OK, thanks. I'll be working on implementing your guidance - probably for the next couple of weeks. Surely I'm not the only simpleton wanting this solution. Seems like Microsoft, or somebidy really clever, like yourself, would have created an "add nerw series" plot function that could be used in an if statement. Oh, well, who am I to complain. Thanks again for pointing me in the right direction and providing the code to get me there. Best regards, Cymon "Jon Peltier" wrote: Write a procedure to take a row of data and process it into the chart. Don't specify a row number, but use a variable lRow to signify the row number. Finally, insert this variable as an argument to the procedu Sub AddRowToChart(lRow As Long) '... ' your code goes here '... End Sub Now switch to the sheet that contains the data. Right click on the sheet tab, and select View Code. This opens a special code module that is attached specifically to the sheet. There are two pull downs at the top of the module. In the left one, select Worksheet. This puts the frame of an event procedure in the module, for Worksheet_SelectionChange. We don't want this one, so you can delete it or ignore it. In the right dropdown, select Change. Now you get the event procedure that we need: Private Sub Worksheet_Change(ByVal Target As Range) End Sub What this is, is a procedure that will run every time the sheet changes (almost every time, but definitely every time a user enters a new value). When it runs, the procedure is passed Target, the range that experienced the change. Suppose your five columns of data are in columns A:E. You want to detect a change in these columns, and send the row of the change to the first procedure. You also might not want to add a row to the sheet if it's already been added. For this purpose, I'll use the easy approach of putting a 1 into column F. So we will not process a row that has 1 in column F. If you construct your added series and labels appropriately, they'll update without needing the event procedure. Pseudo code for the event procedu bail out if changed cell isn't in A:E bail out if column F in the row of the changed cell contains 1 bail out if A don't all have data (too soon to process) Here's a cut at the procedure (watch for newsreader line wraps): Private Sub Worksheet_Change(ByVal Target As Range) 'bail out if changed cell isn't in A:E If Not Intersect(Target, Range("A:E")) Is Nothing Then 'bail out if column F in the row of the changed cell contains 1 If Cells(Target.Row, "F") 1 Then 'bail out if A don't all have data (too soon to process) If Application.CountA(Cells(Target.Row, "A").Resize(1, 5)) = 5 Then AddRowToChart Target.Row Cells(Target.Row, "F").Value = 1 End If End If End If End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CymonM wrote: Jon, I was hoping you would respond. I have visited your site and find it extensive and most helpful. I have the input data (x1,x2, and y1,y2) in columns already, as well as the label for the line, so all the input data for a series in on the same row. Where I falter is in generalizing the recorded macro so it is not specific to the recorded row. I can create a macro by recording my keystrokes, but then my mind goes blank, and I can't seem to then make it general. It seems I would need someway for the macro to use the current row number, and I'm not sure how to proceed from there in changing the specific absolute cell locations to one where the cell reference is a set column, but a variable or current row . I am new to the Visual Basic Code and although you and others have many excellant examples, I am still struggling to generalize the syntax. Any specific coding would be greatly appeciated. Regards, Cymonm "Jon Peltier" wrote: I think the way I'd approach this is to have four columns, X1, X2, Y1, Y2. The user enters the next row of data, and presses a button to add the next series with these values. Instead of a button, you could use the Worksheet_Change event to detect when a new row is populated and add a new series. If these points need labels, then you could have two more columns, Name1, Name2. The event procedure would add these labels to the points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CymonM wrote: Yes, I use named ranges to input some of the data (the node coordinates) as a single series (no connecting lines), and then an add-in labeler to for their label assignments. But each line segment must be an independent series as its assignment to particular nodes is arbitrary. Jon Peltier also has a VBA example on how to input a group of series all at once. But what I would like to do is add each new line series to the chart as it was defined. This is because input error is more easily discovered and corrected this way. "turbodance" wrote: Hi, I have not understood your task very well. Anyway, I'm trying to answer it. Did you try a named range (say, DATA) for series, like DATA = (OFFSET...)...? Is it what you want??? If yes - give me your mail and I'll send you a file -- turbodance ------------------------------------------------------------------------ turbodance's Profile: http://www.excelforum.com/member.php...o&userid=14911 View this thread: http://www.excelforum.com/showthread...hreadid=265402 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Scatter chart series not modifiable from VBA code | TianVanHeerden | Charts and Charting | 7 | July 31st, 2004 11:22 PM |
Excel Chart Vertical Lines Corrupting? | Ken Black | Charts and Charting | 4 | June 4th, 2004 09:16 PM |
Chart menu visible property | Sandy V | Charts and Charting | 8 | May 17th, 2004 01:39 PM |
add vertical line to floating bar chart, still trying | bbxrider | Charts and Charting | 6 | January 27th, 2004 11:42 PM |