ExcelBanter - Sorting chart by name

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sorting chart by name (https://www.excelbanter.com/excel-programming/388032-sorting-chart-name.html)

Sorting chart by name

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1

For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting chart by name

Are you sure it's choosing the chart that's designated as "Chart 6" by Excel,
or a chart that you call "Chart 6"?

"navin" wrote:

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1

For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting chart by name

i checked, actually it takes "Chart 6" designated by excel and not
chart name "Chart 6"

Please help.

Thanks,
navin

Barb Reinhardt wrote:
Are you sure it's choosing the chart that's designated as "Chart 6" by Excel,
or a chart that you call "Chart 6"?

"navin" wrote:

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1

For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting chart by name

You can do this one of two ways.

1 - Look at the title of the chart and sort based upon that
2 - Rename the chart names.

Given what you know about this, which is better for you to do.

"navin" wrote:

i checked, actually it takes "Chart 6" designated by excel and not
chart name "Chart 6"

Please help.

Thanks,
navin

Barb Reinhardt wrote:
Are you sure it's choosing the chart that's designated as "Chart 6" by Excel,
or a chart that you call "Chart 6"?

"navin" wrote:

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1

For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting chart by name

Can you tell me how to sort charts based on the title.

Thanks,
Navin

Barb Reinhardt wrote:
You can do this one of two ways.

1 - Look at the title of the chart and sort based upon that
2 - Rename the chart names.

Given what you know about this, which is better for you to do.

"navin" wrote:

i checked, actually it takes "Chart 6" designated by excel and not
chart name "Chart 6"

Please help.

Thanks,
navin

Barb Reinhardt wrote:
Are you sure it's choosing the chart that's designated as "Chart 6" by Excel,
or a chart that you call "Chart 6"?

"navin" wrote:

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1

For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting chart by name

Unfortunately, I don't have time to go through your code and figure out where
this goes.

This snippet of code should provide enough information for you to be able to
extract the chart title.

Dim aWS As Worksheet
Dim chtObj As ChartObject
For Each aWS In ActiveWorkbook.Worksheets
Debug.Print aWS.Name
For Each chtObj In aWS.ChartObjects
If chtObj.Chart.HasTitle Then
Debug.Print chtObj.Chart.ChartTitle.Text
End If
Next chtObj
Next aWS

"navin" wrote:

Can you tell me how to sort charts based on the title.

Thanks,
Navin

Barb Reinhardt wrote:
You can do this one of two ways.

1 - Look at the title of the chart and sort based upon that
2 - Rename the chart names.

Given what you know about this, which is better for you to do.

"navin" wrote:

i checked, actually it takes "Chart 6" designated by excel and not
chart name "Chart 6"

Please help.

Thanks,
navin

Barb Reinhardt wrote:
Are you sure it's choosing the chart that's designated as "Chart 6" by Excel,
or a chart that you call "Chart 6"?

"navin" wrote:

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1

For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin