Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positionin charts
Hi All,
I have 15 charts in a sheet. I want to place these charts in a way that the sheet contains, 3 charts in a row (i.e) total five columns. any ideas how to achieve this. thanks, navin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positionin charts
Hi,
Try this routine for positioning chart objects. The spacing is determined by the widest/tallest objects. There are 2 variables that can be set to control centering. Sub PosCharts() Dim sngLeft As Single Dim sngTop As Single Dim sngHeight As Single Dim sngMaxWidth As Single Dim sngMaxHeight As Single Dim objCht As ChartObject Dim lngIndex As Long Dim blnHorizontalCenter As Boolean Dim blnVerticalCenter As Boolean For Each objCht In ActiveSheet.ChartObjects If objCht.Width sngMaxWidth Then sngMaxWidth = objCht.Width If objCht.Height sngMaxHeight Then sngMaxHeight = objCht.Height Next blnHorizontalCenter = True blnVerticalCenter = True sngTop = 1 For Each objCht In ActiveSheet.ChartObjects lngIndex = lngIndex + 1 If lngIndex 5 Then ' Number of columns lngIndex = 1 sngTop = sngTop + sngMaxHeight + 1 sngLeft = 1 End If If blnVerticalCenter Then objCht.Top = sngTop + ((sngMaxHeight - objCht.Height) / 2) Else objCht.Top = sngTop End If If blnHorizontalCenter Then objCht.Left = sngLeft + ((sngMaxWidth - objCht.Width) / 2) Else objCht.Left = sngLeft End If sngLeft = sngLeft + sngMaxWidth + 1 Next End Sub Cheers Andy -- -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "navin" wrote in message oups.com... Hi All, I have 15 charts in a sheet. I want to place these charts in a way that the sheet contains, 3 charts in a row (i.e) total five columns. any ideas how to achieve this. thanks, navin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positionin charts
Navin,
Assuming that the only shapes that you have on the activesheet are the 15 charts, try the macro below. See the in-line comments for use of the variables. HTH, Bernie MS Excel MVP Sub SpaceOutCharts() Dim i As Integer Dim j As Integer Dim NumRow As Integer Dim NumCol As Integer Dim FirstRow As Integer Dim FirstCol As Integer Dim RowInc As Integer Dim ColInc As Integer 'Specify 5 rows by 3 columns of charts NumRow = 5 NumCol = 3 'Specify the cell for the first chart - C6 in this example FirstRow = 6 FirstCol = 3 'Specify the spacing of the charts - these depend on your 'chart size and column width and row height - trial and error.... RowInc = 10 ColInc = 5 For i = 0 To NumRow - 1 For j = 0 To NumCol - 1 With ActiveSheet.Shapes(j * NumRow + i + 1) .Left = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Left .Top = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Top End With Next j Next i End Sub "navin" wrote in message oups.com... Hi All, I have 15 charts in a sheet. I want to place these charts in a way that the sheet contains, 3 charts in a row (i.e) total five columns. any ideas how to achieve this. thanks, navin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positionin charts
Thanks for the reply.
I am sorry that i didnt mention that, the sheet also has some data from col A to D and from Column F to M. so dargged the size of col E and positioned all the charts in that with height of 253 and width of 386. so i thought of just adding the top value of the first chart to the height which will give me the top value for my next chart but this is not working. can you tell me as how to adjust all the charts in the column E (row 1 to 97). Thanks for helping. Regards, Navin Bernie Deitrick wrote: Navin, Assuming that the only shapes that you have on the activesheet are the 15 charts, try the macro below. See the in-line comments for use of the variables. HTH, Bernie MS Excel MVP Sub SpaceOutCharts() Dim i As Integer Dim j As Integer Dim NumRow As Integer Dim NumCol As Integer Dim FirstRow As Integer Dim FirstCol As Integer Dim RowInc As Integer Dim ColInc As Integer 'Specify 5 rows by 3 columns of charts NumRow = 5 NumCol = 3 'Specify the cell for the first chart - C6 in this example FirstRow = 6 FirstCol = 3 'Specify the spacing of the charts - these depend on your 'chart size and column width and row height - trial and error.... RowInc = 10 ColInc = 5 For i = 0 To NumRow - 1 For j = 0 To NumCol - 1 With ActiveSheet.Shapes(j * NumRow + i + 1) .Left = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Left .Top = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Top End With Next j Next i End Sub "navin" wrote in message oups.com... Hi All, I have 15 charts in a sheet. I want to place these charts in a way that the sheet contains, 3 charts in a row (i.e) total five columns. any ideas how to achieve this. thanks, navin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positionin charts
Navin,
Sub SpaceOutCharts2() Dim i As Integer Dim NumCharts As Integer Dim myTop As Integer Dim myLeft As Integer Dim TopInc As Integer Dim myCell As Range Set myCell = Range("E1") 'Find the number of charts NumCharts = ActiveSheet.Shapes.Count 'Specify the cell for the first chart - E1 in this example myTop = myCell.Top myLeft = myCell.Left 'Specify the spacing of the charts TopInc = 255 For i = 1 To NumCharts With ActiveSheet.Shapes(i) .Left = myLeft .Top = myTop + TopInc * (i - 1) End With Next i End Sub -- HTH, Bernie MS Excel MVP "navin" wrote in message oups.com... Thanks for the reply. I am sorry that i didnt mention that, the sheet also has some data from col A to D and from Column F to M. so dargged the size of col E and positioned all the charts in that with height of 253 and width of 386. so i thought of just adding the top value of the first chart to the height which will give me the top value for my next chart but this is not working. can you tell me as how to adjust all the charts in the column E (row 1 to 97). Thanks for helping. Regards, Navin Bernie Deitrick wrote: Navin, Assuming that the only shapes that you have on the activesheet are the 15 charts, try the macro below. See the in-line comments for use of the variables. HTH, Bernie MS Excel MVP Sub SpaceOutCharts() Dim i As Integer Dim j As Integer Dim NumRow As Integer Dim NumCol As Integer Dim FirstRow As Integer Dim FirstCol As Integer Dim RowInc As Integer Dim ColInc As Integer 'Specify 5 rows by 3 columns of charts NumRow = 5 NumCol = 3 'Specify the cell for the first chart - C6 in this example FirstRow = 6 FirstCol = 3 'Specify the spacing of the charts - these depend on your 'chart size and column width and row height - trial and error.... RowInc = 10 ColInc = 5 For i = 0 To NumRow - 1 For j = 0 To NumCol - 1 With ActiveSheet.Shapes(j * NumRow + i + 1) .Left = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Left .Top = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Top End With Next j Next i End Sub "navin" wrote in message oups.com... Hi All, I have 15 charts in a sheet. I want to place these charts in a way that the sheet contains, 3 charts in a row (i.e) total five columns. any ideas how to achieve this. thanks, navin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
charts toolbar / charts disappeared | Charts and Charting in Excel | |||
link excel charts to web pages and update charts automatically | Charts and Charting in Excel | |||
Charts - How to have multiple charts share a legend. | Charts and Charting in Excel | |||
interactive charts for stacked bar charts | Charts and Charting in Excel | |||
Matching the colors Column Charts and Pie Charts | Charts and Charting in Excel |