Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
I use excel 2003 and when I try to use the macro recoder to create a custom
chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
The first thing that I would do is avoid using Active<object in general.
There will be exceptions to this rule, but it should be rare in nature as you can run into a lot of issues otherwise to be using these active objects. Now onto chart coding. This is one area all to itself, even within VBA. Charts are easy to learn on the spreadsheet side, but much more complex on the VBA side as it's spread out a lot more so than you would typically think. I actually used the combination of the recorder and the watches to help me learn the charting code a lot more easily, but even then, it's still has some oddities to it. For instance, you want to line one chart directly over the top of another chart, and you want to setup your own code for being able to adjust the scales as the code may need to. Thirdly, you want to have a label to the right of the charts but still within the chart area and on top. This label is to move with the goal line as the scales are adjusted. Well one oddity I found, setting the left, top, width and height of the 2 charts don't necessarily place one directly over the top of the other even though you would think that would have to happen. This can be off by just enough to be visible when this is printed. This is what I had to face in regards to my production charts and get them to look just how people within the company wanted them to look for easy to read purposes. Top chart being a line chart while the bottom chart being an area chart. On the area chart, goal line had to stretch from the left edge to the right edge of the plot area with it being one color above it and another color below it. On the line chart, it had to track performances with the marks in the middle of the columns. Bare in mind, you not only have the chart area, but you also have the plot area too as you will notice in the code below. Given the various oddities and different rules that must be followed, I'm still not done setting up my own codes for the production charts, but I have made some headway with it. First, setup the charts by hand, and then use code to make adjustments to those charts along the way on an as needed basis. This means it would also be wise to name those charts for readability purposes. In this example, I used this code to be able to line up the area chart with the line chart, which was easier said than done as WYSIWYG doesn't apply too easily in the case of working with charts overlaid on top of each other. I ended up using larger border weight around the outside of the plot area on the line chart to hide this fact when the charts prints out. --------BEGIN---CODE-------------- Sub ChartMaintenance() Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long, lngFCHTCOL As Long Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long, lngLCHTCOL As Long Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double, dblCHTWID As Double Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As ChartObject strSFN = "0271321_Production-2.xls" lngFCHTCOL = 2 lngLCHTCOL = 11 lngFROWSET = 41 lngLROWSET = 55 lngFROWRUN = 58 lngLROWRUN = 72 lngFROWPRD = 75 lngLROWPRD = 89 With Workbooks(strSFN).Worksheets("Charts") 'With .ChartObject("chtSetupLine") ' .Left = 54 'End With Set chtOBJ = .ChartObjects("chtRunArea") With chtOBJ Set rngTOPL = Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL) Set rngBTMR = Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL) .Top = rngTOPL.Top .Height = rngBTMR.Top - .Top + rngBTMR.Height .Left = rngTOPL.Left + 1 .Visible = True .Width = rngBTMR.Left - .Left + rngBTMR.Width Set cht = .Chart With cht With .ChartArea .AutoScaleFont = False .Fill.BackColor.SchemeColor = 70 '.Fill.BackColor.Type = 2 .Fill.Visible = msoFalse '.Left = 4 '.Top = 4 End With .HasDataTable = False .HasLegend = False '.HasPivotFields = False .HasTitle = False cht.PlotArea.Left = Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2, rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left + cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2 cht.PlotArea.Width = cht.PlotArea.Width - cht.PlotArea.InsideWidth + 388 cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top - cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13 cht.PlotArea.Height = cht.PlotArea.Height - cht.PlotArea.InsideHeight + 137 End With End With 'With .ChartObject("chtProdLine") ' .Left = 54 'End With End With End Sub ---------END---CODE--------------- Hope this is of help. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... I use excel 2003 and when I try to use the macro recoder to create a custom chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
Thanks Ronald, I will down load the code and step thru it to see how it
works. I do appricate the time you took. By the way, from what you are saying it looks like that vba code does not recognize the custom chart types. If it does then I am really missing something. Again Thanks. "Ronald Dodge" wrote: The first thing that I would do is avoid using Active<object in general. There will be exceptions to this rule, but it should be rare in nature as you can run into a lot of issues otherwise to be using these active objects. Now onto chart coding. This is one area all to itself, even within VBA. Charts are easy to learn on the spreadsheet side, but much more complex on the VBA side as it's spread out a lot more so than you would typically think. I actually used the combination of the recorder and the watches to help me learn the charting code a lot more easily, but even then, it's still has some oddities to it. For instance, you want to line one chart directly over the top of another chart, and you want to setup your own code for being able to adjust the scales as the code may need to. Thirdly, you want to have a label to the right of the charts but still within the chart area and on top. This label is to move with the goal line as the scales are adjusted. Well one oddity I found, setting the left, top, width and height of the 2 charts don't necessarily place one directly over the top of the other even though you would think that would have to happen. This can be off by just enough to be visible when this is printed. This is what I had to face in regards to my production charts and get them to look just how people within the company wanted them to look for easy to read purposes. Top chart being a line chart while the bottom chart being an area chart. On the area chart, goal line had to stretch from the left edge to the right edge of the plot area with it being one color above it and another color below it. On the line chart, it had to track performances with the marks in the middle of the columns. Bare in mind, you not only have the chart area, but you also have the plot area too as you will notice in the code below. Given the various oddities and different rules that must be followed, I'm still not done setting up my own codes for the production charts, but I have made some headway with it. First, setup the charts by hand, and then use code to make adjustments to those charts along the way on an as needed basis. This means it would also be wise to name those charts for readability purposes. In this example, I used this code to be able to line up the area chart with the line chart, which was easier said than done as WYSIWYG doesn't apply too easily in the case of working with charts overlaid on top of each other. I ended up using larger border weight around the outside of the plot area on the line chart to hide this fact when the charts prints out. --------BEGIN---CODE-------------- Sub ChartMaintenance() Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long, lngFCHTCOL As Long Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long, lngLCHTCOL As Long Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double, dblCHTWID As Double Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As ChartObject strSFN = "0271321_Production-2.xls" lngFCHTCOL = 2 lngLCHTCOL = 11 lngFROWSET = 41 lngLROWSET = 55 lngFROWRUN = 58 lngLROWRUN = 72 lngFROWPRD = 75 lngLROWPRD = 89 With Workbooks(strSFN).Worksheets("Charts") 'With .ChartObject("chtSetupLine") ' .Left = 54 'End With Set chtOBJ = .ChartObjects("chtRunArea") With chtOBJ Set rngTOPL = Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL) Set rngBTMR = Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL) .Top = rngTOPL.Top .Height = rngBTMR.Top - .Top + rngBTMR.Height .Left = rngTOPL.Left + 1 .Visible = True .Width = rngBTMR.Left - .Left + rngBTMR.Width Set cht = .Chart With cht With .ChartArea .AutoScaleFont = False .Fill.BackColor.SchemeColor = 70 '.Fill.BackColor.Type = 2 .Fill.Visible = msoFalse '.Left = 4 '.Top = 4 End With .HasDataTable = False .HasLegend = False '.HasPivotFields = False .HasTitle = False cht.PlotArea.Left = Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2, rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left + cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2 cht.PlotArea.Width = cht.PlotArea.Width - cht.PlotArea.InsideWidth + 388 cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top - cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13 cht.PlotArea.Height = cht.PlotArea.Height - cht.PlotArea.InsideHeight + 137 End With End With 'With .ChartObject("chtProdLine") ' .Left = 54 'End With End With End Sub ---------END---CODE--------------- Hope this is of help. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... I use excel 2003 and when I try to use the macro recoder to create a custom chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
I don't want to falsely imply that as I haven't explored that part of it
within VBA, but just from the various issues that I have ran into on the VBA side, I generally found it to be better to setup by hand first, then use VBA to control after that. This is one area where I'm also having to keep digging deeper down, as I seem to do that with a lot of things including getting PDF Files to be read by Excel VBA (requires either standard or professional version of Adobe on the system though for this to work), Excel VBA interacting with ShowCase Strategy Query (expected), IBM Personal Communicator (used to connect to the DB and do some things within the DB to the extent users have priviledges), the other MS Office programs including MS Project, and even to some extent, Lotus Notes for sending emails. Anyhow, when I looked at the various custom charts, there was none that met the combo that I needed, so I ended up having to create my own via the 2 chart process. A few of them were close, but was off by one aspect or another aspect. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... Thanks Ronald, I will down load the code and step thru it to see how it works. I do appricate the time you took. By the way, from what you are saying it looks like that vba code does not recognize the custom chart types. If it does then I am really missing something. Again Thanks. "Ronald Dodge" wrote: The first thing that I would do is avoid using Active<object in general. There will be exceptions to this rule, but it should be rare in nature as you can run into a lot of issues otherwise to be using these active objects. Now onto chart coding. This is one area all to itself, even within VBA. Charts are easy to learn on the spreadsheet side, but much more complex on the VBA side as it's spread out a lot more so than you would typically think. I actually used the combination of the recorder and the watches to help me learn the charting code a lot more easily, but even then, it's still has some oddities to it. For instance, you want to line one chart directly over the top of another chart, and you want to setup your own code for being able to adjust the scales as the code may need to. Thirdly, you want to have a label to the right of the charts but still within the chart area and on top. This label is to move with the goal line as the scales are adjusted. Well one oddity I found, setting the left, top, width and height of the 2 charts don't necessarily place one directly over the top of the other even though you would think that would have to happen. This can be off by just enough to be visible when this is printed. This is what I had to face in regards to my production charts and get them to look just how people within the company wanted them to look for easy to read purposes. Top chart being a line chart while the bottom chart being an area chart. On the area chart, goal line had to stretch from the left edge to the right edge of the plot area with it being one color above it and another color below it. On the line chart, it had to track performances with the marks in the middle of the columns. Bare in mind, you not only have the chart area, but you also have the plot area too as you will notice in the code below. Given the various oddities and different rules that must be followed, I'm still not done setting up my own codes for the production charts, but I have made some headway with it. First, setup the charts by hand, and then use code to make adjustments to those charts along the way on an as needed basis. This means it would also be wise to name those charts for readability purposes. In this example, I used this code to be able to line up the area chart with the line chart, which was easier said than done as WYSIWYG doesn't apply too easily in the case of working with charts overlaid on top of each other. I ended up using larger border weight around the outside of the plot area on the line chart to hide this fact when the charts prints out. --------BEGIN---CODE-------------- Sub ChartMaintenance() Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long, lngFCHTCOL As Long Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long, lngLCHTCOL As Long Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double, dblCHTWID As Double Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As ChartObject strSFN = "0271321_Production-2.xls" lngFCHTCOL = 2 lngLCHTCOL = 11 lngFROWSET = 41 lngLROWSET = 55 lngFROWRUN = 58 lngLROWRUN = 72 lngFROWPRD = 75 lngLROWPRD = 89 With Workbooks(strSFN).Worksheets("Charts") 'With .ChartObject("chtSetupLine") ' .Left = 54 'End With Set chtOBJ = .ChartObjects("chtRunArea") With chtOBJ Set rngTOPL = Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL) Set rngBTMR = Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL) .Top = rngTOPL.Top .Height = rngBTMR.Top - .Top + rngBTMR.Height .Left = rngTOPL.Left + 1 .Visible = True .Width = rngBTMR.Left - .Left + rngBTMR.Width Set cht = .Chart With cht With .ChartArea .AutoScaleFont = False .Fill.BackColor.SchemeColor = 70 '.Fill.BackColor.Type = 2 .Fill.Visible = msoFalse '.Left = 4 '.Top = 4 End With .HasDataTable = False .HasLegend = False '.HasPivotFields = False .HasTitle = False cht.PlotArea.Left = Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2, rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left + cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2 cht.PlotArea.Width = cht.PlotArea.Width - cht.PlotArea.InsideWidth + 388 cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top - cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13 cht.PlotArea.Height = cht.PlotArea.Height - cht.PlotArea.InsideHeight + 137 End With End With 'With .ChartObject("chtProdLine") ' .Left = 54 'End With End With End Sub ---------END---CODE--------------- Hope this is of help. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... I use excel 2003 and when I try to use the macro recoder to create a custom chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
I haven't followed the whole thread but -
from what you are saying it looks like that vba code does not recognize the custom chart types Of course it does! As far as VBA is concerned a custom chart is one with two or more series with two or more ChartType's. You can build your own custom chart from scratch with VBA, or adapt an existing chart (assuming of course it has at least two series). Regards, Peter T PS had a quick look at your OP, not sure what the problem is, the code as posted works. You could apply one of the built-in custom types with VBA or better still do entirely with VBA "John" wrote in message ... Thanks Ronald, I will down load the code and step thru it to see how it works. I do appricate the time you took. By the way, from what you are saying it looks like that vba code does not recognize the custom chart types. If it does then I am really missing something. Again Thanks. "Ronald Dodge" wrote: The first thing that I would do is avoid using Active<object in general. There will be exceptions to this rule, but it should be rare in nature as you can run into a lot of issues otherwise to be using these active objects. Now onto chart coding. This is one area all to itself, even within VBA. Charts are easy to learn on the spreadsheet side, but much more complex on the VBA side as it's spread out a lot more so than you would typically think. I actually used the combination of the recorder and the watches to help me learn the charting code a lot more easily, but even then, it's still has some oddities to it. For instance, you want to line one chart directly over the top of another chart, and you want to setup your own code for being able to adjust the scales as the code may need to. Thirdly, you want to have a label to the right of the charts but still within the chart area and on top. This label is to move with the goal line as the scales are adjusted. Well one oddity I found, setting the left, top, width and height of the 2 charts don't necessarily place one directly over the top of the other even though you would think that would have to happen. This can be off by just enough to be visible when this is printed. This is what I had to face in regards to my production charts and get them to look just how people within the company wanted them to look for easy to read purposes. Top chart being a line chart while the bottom chart being an area chart. On the area chart, goal line had to stretch from the left edge to the right edge of the plot area with it being one color above it and another color below it. On the line chart, it had to track performances with the marks in the middle of the columns. Bare in mind, you not only have the chart area, but you also have the plot area too as you will notice in the code below. Given the various oddities and different rules that must be followed, I'm still not done setting up my own codes for the production charts, but I have made some headway with it. First, setup the charts by hand, and then use code to make adjustments to those charts along the way on an as needed basis. This means it would also be wise to name those charts for readability purposes. In this example, I used this code to be able to line up the area chart with the line chart, which was easier said than done as WYSIWYG doesn't apply too easily in the case of working with charts overlaid on top of each other. I ended up using larger border weight around the outside of the plot area on the line chart to hide this fact when the charts prints out. --------BEGIN---CODE-------------- Sub ChartMaintenance() Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long, lngFCHTCOL As Long Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long, lngLCHTCOL As Long Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double, dblCHTWID As Double Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As ChartObject strSFN = "0271321_Production-2.xls" lngFCHTCOL = 2 lngLCHTCOL = 11 lngFROWSET = 41 lngLROWSET = 55 lngFROWRUN = 58 lngLROWRUN = 72 lngFROWPRD = 75 lngLROWPRD = 89 With Workbooks(strSFN).Worksheets("Charts") 'With .ChartObject("chtSetupLine") ' .Left = 54 'End With Set chtOBJ = .ChartObjects("chtRunArea") With chtOBJ Set rngTOPL = Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL) Set rngBTMR = Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL) .Top = rngTOPL.Top .Height = rngBTMR.Top - .Top + rngBTMR.Height .Left = rngTOPL.Left + 1 .Visible = True .Width = rngBTMR.Left - .Left + rngBTMR.Width Set cht = .Chart With cht With .ChartArea .AutoScaleFont = False .Fill.BackColor.SchemeColor = 70 '.Fill.BackColor.Type = 2 .Fill.Visible = msoFalse '.Left = 4 '.Top = 4 End With .HasDataTable = False .HasLegend = False '.HasPivotFields = False .HasTitle = False cht.PlotArea.Left = Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2, rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left + cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2 cht.PlotArea.Width = cht.PlotArea.Width - cht.PlotArea.InsideWidth + 388 cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top - cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13 cht.PlotArea.Height = cht.PlotArea.Height - cht.PlotArea.InsideHeight + 137 End With End With 'With .ChartObject("chtProdLine") ' .Left = 54 'End With End With End Sub ---------END---CODE--------------- Hope this is of help. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... I use excel 2003 and when I try to use the macro recoder to create a custom chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
John -
Recorded macros aren't always perfect. One issue that often occurs is that the recorded macro shows the chart type applied before the data, and in general if you don't get the chart type you intended, you should apply it after the chart has data. This minor variation on your procedure works fine (note the new position of the ApplyCustomType statement): Sub DoChart() Charts.Add ActiveChart.SetSourceData _ Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub I almost never use these built-in custom types, because if you add a series, it may not be the type you expected, and it may change the types of existing series. I apply one regular type to the entire chart, then apply a different type to the particular series, and if necessary change the axes. Sub DoChart() Charts.Add ActiveChart.SetSourceData _ Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" ActiveChart.ChartType = xlColumnClustered ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub In general Ronald is correct about using ActiveChart or any other selected object. This macro is pretty simple, so it's probably not worth fussing with. However, the way to avoid dealing with an active chart is as follows. For a more detailed discussion, see this page: http://peltiertech.com/Excel/ChartsH...kChartVBA.html Sub DoChart() Dim wksht As Worksheet Dim cht As Chart Dim width As Double Dim height As Double width = ActiveWindow.UsableWidth height = ActiveWindow.UsableHeight Set wksht = ActiveSheet Set cht = wksht.ChartObjects.Add(width / 4, height / 4, width / 2, height / 2).Chart With cht .SetSourceData _ Source:=wksht.Range("B37:D61"), PlotBy:=xlColumns .ChartType = xlColumnClustered With .SeriesCollection(2) .ChartType = xlLineMarkers .AxisGroup = xlSecondary End With With .Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With .Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With .HasLegend = True With .Legend .Position = xlBottom With .Interior .ColorIndex = 36 .Pattern = 1 End With End With End With End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Ronald Dodge" wrote in message ... The first thing that I would do is avoid using Active<object in general. There will be exceptions to this rule, but it should be rare in nature as you can run into a lot of issues otherwise to be using these active objects. Now onto chart coding. This is one area all to itself, even within VBA. Charts are easy to learn on the spreadsheet side, but much more complex on the VBA side as it's spread out a lot more so than you would typically think. I actually used the combination of the recorder and the watches to help me learn the charting code a lot more easily, but even then, it's still has some oddities to it. For instance, you want to line one chart directly over the top of another chart, and you want to setup your own code for being able to adjust the scales as the code may need to. Thirdly, you want to have a label to the right of the charts but still within the chart area and on top. This label is to move with the goal line as the scales are adjusted. Well one oddity I found, setting the left, top, width and height of the 2 charts don't necessarily place one directly over the top of the other even though you would think that would have to happen. This can be off by just enough to be visible when this is printed. This is what I had to face in regards to my production charts and get them to look just how people within the company wanted them to look for easy to read purposes. Top chart being a line chart while the bottom chart being an area chart. On the area chart, goal line had to stretch from the left edge to the right edge of the plot area with it being one color above it and another color below it. On the line chart, it had to track performances with the marks in the middle of the columns. Bare in mind, you not only have the chart area, but you also have the plot area too as you will notice in the code below. Given the various oddities and different rules that must be followed, I'm still not done setting up my own codes for the production charts, but I have made some headway with it. First, setup the charts by hand, and then use code to make adjustments to those charts along the way on an as needed basis. This means it would also be wise to name those charts for readability purposes. In this example, I used this code to be able to line up the area chart with the line chart, which was easier said than done as WYSIWYG doesn't apply too easily in the case of working with charts overlaid on top of each other. I ended up using larger border weight around the outside of the plot area on the line chart to hide this fact when the charts prints out. --------BEGIN---CODE-------------- Sub ChartMaintenance() Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long, lngFCHTCOL As Long Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long, lngLCHTCOL As Long Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double, dblCHTWID As Double Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As ChartObject strSFN = "0271321_Production-2.xls" lngFCHTCOL = 2 lngLCHTCOL = 11 lngFROWSET = 41 lngLROWSET = 55 lngFROWRUN = 58 lngLROWRUN = 72 lngFROWPRD = 75 lngLROWPRD = 89 With Workbooks(strSFN).Worksheets("Charts") 'With .ChartObject("chtSetupLine") ' .Left = 54 'End With Set chtOBJ = .ChartObjects("chtRunArea") With chtOBJ Set rngTOPL = Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL) Set rngBTMR = Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL) .Top = rngTOPL.Top .Height = rngBTMR.Top - .Top + rngBTMR.Height .Left = rngTOPL.Left + 1 .Visible = True .Width = rngBTMR.Left - .Left + rngBTMR.Width Set cht = .Chart With cht With .ChartArea .AutoScaleFont = False .Fill.BackColor.SchemeColor = 70 '.Fill.BackColor.Type = 2 .Fill.Visible = msoFalse '.Left = 4 '.Top = 4 End With .HasDataTable = False .HasLegend = False '.HasPivotFields = False .HasTitle = False cht.PlotArea.Left = Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2, rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left + cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2 cht.PlotArea.Width = cht.PlotArea.Width - cht.PlotArea.InsideWidth + 388 cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top - cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13 cht.PlotArea.Height = cht.PlotArea.Height - cht.PlotArea.InsideHeight + 137 End With End With 'With .ChartObject("chtProdLine") ' .Left = 54 'End With End With End Sub ---------END---CODE--------------- Hope this is of help. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... I use excel 2003 and when I try to use the macro recoder to create a custom chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program a combination chart using VBA
Some of this stuff, while I can see Jon's point about not having to fuss
about using Active objects for small scale stuff, some of this just comes from experience and this Active stuff was one of the first things that bit me hard in VBA coding, which meant I had to go back and redo enough things with the Active object codes. On the other hand, there are most definitely exceptions to this rule. This Active stuff was originally created by the macro recorder, which is also to make a point, while the macro recorder does help in the learning process, you must be able to go back and make the necessary adjustments as needed to be able to avoid issues down the road. In some sense, this is also true about prequalifications of objects and variables. After many of the issues that I ran into in Access coding, I now by default in most cases prequalify my objects/variables. I know in the case of Excel, there may not be many places where such issues may come into play, but I could definitely see it coming into play when dealing with Chart coding, given the various Left, Top, Width, and Height properties. Main thing though to be careful about, while something may start out small, but as you get to building it up, it no longer is small, and that's when you may be bound to run into some of these issues more often. The larger these projects gets, the more time it takes to convert them, and that's basically how I ended up dealing with this issue in my Production Reporting System, which started out with a very small amount of VBA coding, but as things progressed, that VBA code got bigger and bigger. I now by default look for these things, that deals with good programming practices, even with the fact that I'm a lone developer, not working on a team of developers. However, even though others may not be looking at my code, I still need to have it setup for readability purposes while trying to keep the code as efficient as possible so as when I go back to the code, I only spend minimal time reviewing to see what its purpose is and what it's doing. There's a lot of different rules and guidelines I use as I develop stuff. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Jon Peltier" wrote in message ... John - Recorded macros aren't always perfect. One issue that often occurs is that the recorded macro shows the chart type applied before the data, and in general if you don't get the chart type you intended, you should apply it after the chart has data. This minor variation on your procedure works fine (note the new position of the ApplyCustomType statement): Sub DoChart() Charts.Add ActiveChart.SetSourceData _ Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub I almost never use these built-in custom types, because if you add a series, it may not be the type you expected, and it may change the types of existing series. I apply one regular type to the entire chart, then apply a different type to the particular series, and if necessary change the axes. Sub DoChart() Charts.Add ActiveChart.SetSourceData _ Source:=Sheets("Data").Range("B37:D61"), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" ActiveChart.ChartType = xlColumnClustered ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub In general Ronald is correct about using ActiveChart or any other selected object. This macro is pretty simple, so it's probably not worth fussing with. However, the way to avoid dealing with an active chart is as follows. For a more detailed discussion, see this page: http://peltiertech.com/Excel/ChartsH...kChartVBA.html Sub DoChart() Dim wksht As Worksheet Dim cht As Chart Dim width As Double Dim height As Double width = ActiveWindow.UsableWidth height = ActiveWindow.UsableHeight Set wksht = ActiveSheet Set cht = wksht.ChartObjects.Add(width / 4, height / 4, width / 2, height / 2).Chart With cht .SetSourceData _ Source:=wksht.Range("B37:D61"), PlotBy:=xlColumns .ChartType = xlColumnClustered With .SeriesCollection(2) .ChartType = xlLineMarkers .AxisGroup = xlSecondary End With With .Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With .Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With .HasLegend = True With .Legend .Position = xlBottom With .Interior .ColorIndex = 36 .Pattern = 1 End With End With End With End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Ronald Dodge" wrote in message ... The first thing that I would do is avoid using Active<object in general. There will be exceptions to this rule, but it should be rare in nature as you can run into a lot of issues otherwise to be using these active objects. Now onto chart coding. This is one area all to itself, even within VBA. Charts are easy to learn on the spreadsheet side, but much more complex on the VBA side as it's spread out a lot more so than you would typically think. I actually used the combination of the recorder and the watches to help me learn the charting code a lot more easily, but even then, it's still has some oddities to it. For instance, you want to line one chart directly over the top of another chart, and you want to setup your own code for being able to adjust the scales as the code may need to. Thirdly, you want to have a label to the right of the charts but still within the chart area and on top. This label is to move with the goal line as the scales are adjusted. Well one oddity I found, setting the left, top, width and height of the 2 charts don't necessarily place one directly over the top of the other even though you would think that would have to happen. This can be off by just enough to be visible when this is printed. This is what I had to face in regards to my production charts and get them to look just how people within the company wanted them to look for easy to read purposes. Top chart being a line chart while the bottom chart being an area chart. On the area chart, goal line had to stretch from the left edge to the right edge of the plot area with it being one color above it and another color below it. On the line chart, it had to track performances with the marks in the middle of the columns. Bare in mind, you not only have the chart area, but you also have the plot area too as you will notice in the code below. Given the various oddities and different rules that must be followed, I'm still not done setting up my own codes for the production charts, but I have made some headway with it. First, setup the charts by hand, and then use code to make adjustments to those charts along the way on an as needed basis. This means it would also be wise to name those charts for readability purposes. In this example, I used this code to be able to line up the area chart with the line chart, which was easier said than done as WYSIWYG doesn't apply too easily in the case of working with charts overlaid on top of each other. I ended up using larger border weight around the outside of the plot area on the line chart to hide this fact when the charts prints out. --------BEGIN---CODE-------------- Sub ChartMaintenance() Dim lngFROWSET As Long, lngFROWRUN As Long, lngFROWPRD As Long, lngFCHTCOL As Long Dim lngLROWSET As Long, lngLROWRUN As Long, lngLROWPRD As Long, lngLCHTCOL As Long Dim dblHGTSET As Double, dblHGTRUN As Double, dblHGTPRD As Double, dblCHTWID As Double Dim rngTOPL As Range, rngBTMR As Range, cht As Chart, chtOBJ As ChartObject strSFN = "0271321_Production-2.xls" lngFCHTCOL = 2 lngLCHTCOL = 11 lngFROWSET = 41 lngLROWSET = 55 lngFROWRUN = 58 lngLROWRUN = 72 lngFROWPRD = 75 lngLROWPRD = 89 With Workbooks(strSFN).Worksheets("Charts") 'With .ChartObject("chtSetupLine") ' .Left = 54 'End With Set chtOBJ = .ChartObjects("chtRunArea") With chtOBJ Set rngTOPL = Workbooks(strSFN).Worksheets("Charts").Cells(lngFR OWRUN, lngFCHTCOL) Set rngBTMR = Workbooks(strSFN).Worksheets("Charts").Cells(lngLR OWRUN, lngLCHTCOL) .Top = rngTOPL.Top .Height = rngBTMR.Top - .Top + rngBTMR.Height .Left = rngTOPL.Left + 1 .Visible = True .Width = rngBTMR.Left - .Left + rngBTMR.Width Set cht = .Chart With cht With .ChartArea .AutoScaleFont = False .Fill.BackColor.SchemeColor = 70 '.Fill.BackColor.Type = 2 .Fill.Visible = msoFalse '.Left = 4 '.Top = 4 End With .HasDataTable = False .HasLegend = False '.HasPivotFields = False .HasTitle = False cht.PlotArea.Left = Workbooks(strSFN).Worksheets("Charts").Cells(rngTO PL.Row + 2, rngTOPL.Column + 1).Left - chtOBJ.Left - cht.ChartArea.Left + cht.PlotArea.Left - cht.PlotArea.InsideLeft - 2 cht.PlotArea.Width = cht.PlotArea.Width - cht.PlotArea.InsideWidth + 388 cht.PlotArea.Top = rngTOPL.Offset(2, 0).Top - chtOBJ.Top - cht.ChartArea.Top + cht.PlotArea.Top - cht.PlotArea.InsideTop - 13 cht.PlotArea.Height = cht.PlotArea.Height - cht.PlotArea.InsideHeight + 137 End With End With 'With .ChartObject("chtProdLine") ' .Left = 54 'End With End With End Sub ---------END---CODE--------------- Hope this is of help. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "John" wrote in message ... I use excel 2003 and when I try to use the macro recoder to create a custom chart type of "Line - Column on 2 Axes" the recoder creates the following code and an example of the data is below the code. The chart that is creaded the first time when recording the macro is perfect. However when I try to excute the macro I either receive an error message runtime error 1004, "Methods axes of object - chart failed when I name the axes of the Series or I simply have a column chart and not a combination chart. What I am trying to do is have a single chart that shows staff in column and Volumn in line by time of day. I can make it work perfect by hand but I am unable to write a VBA routine that will accomplish the same thing again. VBA acts like it doesn't reconize the Chart Type. Please help and Thanks in advance 'Macro Code Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SetSourceData Source:=Sheets("Data").Range("B37:D61"),PlotBy:= _ xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom With Selection.Interior .ColorIndex = 36 .Pattern = 1 End With End Sub Spread Sheet Data Col B Col C Col D Row 37 Hours Staff Volumn Row 38 0:00 2 52280 Row 39 1:00 2 52280 Row 40 2:00 2 52280 Row 41 3:00 1 52280 Row 42 4:00 0 52280 Row 43 5:00 0 52280 Row 44 6:00 0 52280 Row 45 7:00 0 52280 Row 46 8:00 0 95280 Row 47 9:00 0 58380 Row 48 10:00 0 52380 Row 49 11:00 0 58980 Row 50 12:00 4 81280 Row 51 13:00 4 117080 Row 52 14:00 4 70880 Row 53 15:00 3 96100 Row 54 16:00 4 91900 Row 55 17:00 4 27600 Row 56 18:00 3 62300 Row 57 19:00 2 44900 Row 58 20:00 2 22700 Row 59 21:00 2 9000 Row 60 22:00 2 0 Row 61 23:00 2 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination chart | Charts and Charting in Excel | |||
Combination of stock chart and line chart | Charts and Charting in Excel | |||
combination chart | Charts and Charting in Excel | |||
How to lock combination chart in pivot chart? | Charts and Charting in Excel | |||
combination chart | Charts and Charting in Excel |