Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart size in relation to column width
Hi,
I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart size in relation to column width
Hi,
You could try this. The chart will not be resize. Only it's top left position will be change to align with the anchor cell E4. '------------------------------------------ Sub AlignColsWithChartCols() ' ' Attempt to align worksheet columns with chart ' columns ' Dim rngAlign As Range Dim chtTemp As ChartObject Dim sngColWidth As Single Dim lngPointIndex As Long ' top left position for chart Set rngAlign = Range("E4") Set chtTemp = ActiveSheet.ChartObjects(1) Application.ScreenUpdating = False With chtTemp .Left = rngAlign.Left .Top = rngAlign.Top .Placement = xlFreeFloating With .Chart sngColWidth = .PlotArea.InsideWidth / ..SeriesCollection(1).Points.Count End With End With ' Adjust gap between chart and plotarea m_AdjustColWidth rngAlign, _ (chtTemp.Chart.ChartArea.Left + chtTemp.Chart.PlotArea.InsideLeft) Set rngAlign = rngAlign.Offset(0, 1) ' adjust columns For lngPointIndex = 1 To chtTemp.Chart.SeriesCollection(1).Points.Count m_AdjustColWidth rngAlign, sngColWidth Set rngAlign = rngAlign.Offset(0, 1) Next Application.ScreenUpdating = True End Sub Private Sub m_AdjustColWidth(Col As Range, Size As Single) Col.ColumnWidth = 1 Do While (Col.Offset(0, 1).Left - Col.Left) < Size Col.ColumnWidth = Col.ColumnWidth + 1 Loop Do While (Col.Offset(0, 1).Left - Col.Left) Size Col.ColumnWidth = Col.ColumnWidth - 0.1 Loop End Sub '------------------------------------------ Obviously run this on a test file. Please post back and let me know if it worked for your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi, I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart size in relation to column width
Hi Andy,
Thnaks for the quick response. I'll look over the code, need to chage it to C# anywayand see if this works. What i don't think i stated in the first post is that i want to line up each category on the X axis with a seperate column in the background. Not sure if your code does that until i give it a try but am going to do this next. Thanks again. G "Andy Pope" wrote: Hi, You could try this. The chart will not be resize. Only it's top left position will be change to align with the anchor cell E4. '------------------------------------------ Sub AlignColsWithChartCols() ' ' Attempt to align worksheet columns with chart ' columns ' Dim rngAlign As Range Dim chtTemp As ChartObject Dim sngColWidth As Single Dim lngPointIndex As Long ' top left position for chart Set rngAlign = Range("E4") Set chtTemp = ActiveSheet.ChartObjects(1) Application.ScreenUpdating = False With chtTemp .Left = rngAlign.Left .Top = rngAlign.Top .Placement = xlFreeFloating With .Chart sngColWidth = .PlotArea.InsideWidth / .SeriesCollection(1).Points.Count End With End With ' Adjust gap between chart and plotarea m_AdjustColWidth rngAlign, _ (chtTemp.Chart.ChartArea.Left + chtTemp.Chart.PlotArea.InsideLeft) Set rngAlign = rngAlign.Offset(0, 1) ' adjust columns For lngPointIndex = 1 To chtTemp.Chart.SeriesCollection(1).Points.Count m_AdjustColWidth rngAlign, sngColWidth Set rngAlign = rngAlign.Offset(0, 1) Next Application.ScreenUpdating = True End Sub Private Sub m_AdjustColWidth(Col As Range, Size As Single) Col.ColumnWidth = 1 Do While (Col.Offset(0, 1).Left - Col.Left) < Size Col.ColumnWidth = Col.ColumnWidth + 1 Loop Do While (Col.Offset(0, 1).Left - Col.Left) Size Col.ColumnWidth = Col.ColumnWidth - 0.1 Loop End Sub '------------------------------------------ Obviously run this on a test file. Please post back and let me know if it worked for your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi, I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart size in relation to column width
Hi Andy,
Thnaks so much for the help on this. Its nearly there, there is however an offset in the chart/column line up which appears to get worse as you look at the categories from left to right. Is this anything you would be able to help with or is this a limitation with how the cells / chart dimensions are measured. Thanks, G "Andy Pope" wrote: Hi, You could try this. The chart will not be resize. Only it's top left position will be change to align with the anchor cell E4. '------------------------------------------ Sub AlignColsWithChartCols() ' ' Attempt to align worksheet columns with chart ' columns ' Dim rngAlign As Range Dim chtTemp As ChartObject Dim sngColWidth As Single Dim lngPointIndex As Long ' top left position for chart Set rngAlign = Range("E4") Set chtTemp = ActiveSheet.ChartObjects(1) Application.ScreenUpdating = False With chtTemp .Left = rngAlign.Left .Top = rngAlign.Top .Placement = xlFreeFloating With .Chart sngColWidth = .PlotArea.InsideWidth / .SeriesCollection(1).Points.Count End With End With ' Adjust gap between chart and plotarea m_AdjustColWidth rngAlign, _ (chtTemp.Chart.ChartArea.Left + chtTemp.Chart.PlotArea.InsideLeft) Set rngAlign = rngAlign.Offset(0, 1) ' adjust columns For lngPointIndex = 1 To chtTemp.Chart.SeriesCollection(1).Points.Count m_AdjustColWidth rngAlign, sngColWidth Set rngAlign = rngAlign.Offset(0, 1) Next Application.ScreenUpdating = True End Sub Private Sub m_AdjustColWidth(Col As Range, Size As Single) Col.ColumnWidth = 1 Do While (Col.Offset(0, 1).Left - Col.Left) < Size Col.ColumnWidth = Col.ColumnWidth + 1 Loop Do While (Col.Offset(0, 1).Left - Col.Left) Size Col.ColumnWidth = Col.ColumnWidth - 0.1 Loop End Sub '------------------------------------------ Obviously run this on a test file. Please post back and let me know if it worked for your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi, I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart size in relation to column width
You could adjust the value in the m_AdjustColWidth routine so the final
adjustment is smaller. Also make sure the sheets zoom setting is 100%. How many columns in your chart? Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi Andy, Thnaks so much for the help on this. Its nearly there, there is however an offset in the chart/column line up which appears to get worse as you look at the categories from left to right. Is this anything you would be able to help with or is this a limitation with how the cells / chart dimensions are measured. Thanks, G "Andy Pope" wrote: Hi, You could try this. The chart will not be resize. Only it's top left position will be change to align with the anchor cell E4. '------------------------------------------ Sub AlignColsWithChartCols() ' ' Attempt to align worksheet columns with chart ' columns ' Dim rngAlign As Range Dim chtTemp As ChartObject Dim sngColWidth As Single Dim lngPointIndex As Long ' top left position for chart Set rngAlign = Range("E4") Set chtTemp = ActiveSheet.ChartObjects(1) Application.ScreenUpdating = False With chtTemp .Left = rngAlign.Left .Top = rngAlign.Top .Placement = xlFreeFloating With .Chart sngColWidth = .PlotArea.InsideWidth / .SeriesCollection(1).Points.Count End With End With ' Adjust gap between chart and plotarea m_AdjustColWidth rngAlign, _ (chtTemp.Chart.ChartArea.Left + chtTemp.Chart.PlotArea.InsideLeft) Set rngAlign = rngAlign.Offset(0, 1) ' adjust columns For lngPointIndex = 1 To chtTemp.Chart.SeriesCollection(1).Points.Count m_AdjustColWidth rngAlign, sngColWidth Set rngAlign = rngAlign.Offset(0, 1) Next Application.ScreenUpdating = True End Sub Private Sub m_AdjustColWidth(Col As Range, Size As Single) Col.ColumnWidth = 1 Do While (Col.Offset(0, 1).Left - Col.Left) < Size Col.ColumnWidth = Col.ColumnWidth + 1 Loop Do While (Col.Offset(0, 1).Left - Col.Left) Size Col.ColumnWidth = Col.ColumnWidth - 0.1 Loop End Sub '------------------------------------------ Obviously run this on a test file. Please post back and let me know if it worked for your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi, I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart size in relation to column width
At the moment 5, this is dynamic though. The functions will handle this by
the looks of it. Changing the precision worked absolutely fine in the adjust function. Thank you so much for this. It was beginning to be a bit of a bug bear. G "Andy Pope" wrote: You could adjust the value in the m_AdjustColWidth routine so the final adjustment is smaller. Also make sure the sheets zoom setting is 100%. How many columns in your chart? Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi Andy, Thnaks so much for the help on this. Its nearly there, there is however an offset in the chart/column line up which appears to get worse as you look at the categories from left to right. Is this anything you would be able to help with or is this a limitation with how the cells / chart dimensions are measured. Thanks, G "Andy Pope" wrote: Hi, You could try this. The chart will not be resize. Only it's top left position will be change to align with the anchor cell E4. '------------------------------------------ Sub AlignColsWithChartCols() ' ' Attempt to align worksheet columns with chart ' columns ' Dim rngAlign As Range Dim chtTemp As ChartObject Dim sngColWidth As Single Dim lngPointIndex As Long ' top left position for chart Set rngAlign = Range("E4") Set chtTemp = ActiveSheet.ChartObjects(1) Application.ScreenUpdating = False With chtTemp .Left = rngAlign.Left .Top = rngAlign.Top .Placement = xlFreeFloating With .Chart sngColWidth = .PlotArea.InsideWidth / .SeriesCollection(1).Points.Count End With End With ' Adjust gap between chart and plotarea m_AdjustColWidth rngAlign, _ (chtTemp.Chart.ChartArea.Left + chtTemp.Chart.PlotArea.InsideLeft) Set rngAlign = rngAlign.Offset(0, 1) ' adjust columns For lngPointIndex = 1 To chtTemp.Chart.SeriesCollection(1).Points.Count m_AdjustColWidth rngAlign, sngColWidth Set rngAlign = rngAlign.Offset(0, 1) Next Application.ScreenUpdating = True End Sub Private Sub m_AdjustColWidth(Col As Range, Size As Single) Col.ColumnWidth = 1 Do While (Col.Offset(0, 1).Left - Col.Left) < Size Col.ColumnWidth = Col.ColumnWidth + 1 Loop Do While (Col.Offset(0, 1).Left - Col.Left) Size Col.ColumnWidth = Col.ColumnWidth - 0.1 Loop End Sub '------------------------------------------ Obviously run this on a test file. Please post back and let me know if it worked for your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GS80" wrote in message ... Hi, I'm working with the Excel 2007 object model. I am trying to create an output sheet where i can position data and cells side by side in a specific order. The problem i am having is when trying to relate column widths with chart objects. I want to be able to get the size of the plot area and then size a cell or multiple cells based on this value. I then plan to move the chart into a position over the columns so that data above and below the chart lines up as i want it. How do the column sizes relate to the chart sizing? Any help on this would be greatly appreciated. G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form button size with column width | Excel Discussion (Misc queries) | |||
Changed column width keeps reverting back to original size | Excel Discussion (Misc queries) | |||
Is it possible to view or size column width and/or row height in . | Excel Discussion (Misc queries) | |||
Varying column width in a column chart | Charts and Charting in Excel | |||
Change the width of a single column in a column chart | Charts and Charting in Excel |