Chart Size

kritimehrotra

New Member
Joined
Jul 18, 2006
Messages
7
Hi,

I have a sheet in Excel with about 8 small graphs. They are all "100% Stacked Bars with Cylindrical Shape". These charts are generated using VBA code, with each run of a program, since their data changes each time. What happens is, sometimes, the chart size of one or two of the charts just shrinks to about half its normal width. The chart area remains the same, and seemingly so does the plot area, but the actual chart itself reduces in size. Could anyone help figure out why that would happen?

Also, I have taken print-screen'ed bmps of the "good" scenario and the "bad" scenario, but am not sure how to attach them with my post.

Thank you very much,
Kriti
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Is your Y axis scale format set to "Auto"? If not your chart will jump to sizes that may not be what you want.
You say your chart is generated using VBA code, if you post the code someone may be able to spot something there.
 

kritimehrotra

New Member
Joined
Jul 18, 2006
Messages
7
Hi,

Thanks so much for the quick reply. I just checked and the Y axis scale format is "Automatic", is that how it should be or should it be changed?

Here is the sub I use to generate the chart. For the charts I am referring to right now, the following are the arguments sent:
strLevel: "REGIONAL"
subNum: 2
strTitle: One of "A", "B", "C", "D" "E", "F", "G", "H"



Code:
Private Sub genChart(xlSht As Object, rngData As Range, strTitle As String, _
   strLevel As String, Optional subNum As Integer)
   
   Dim iLeft As Integer, iWidth As Integer, _
      iTop As Integer, iHeight As Integer, _
      iFontSize As Integer
   
   Select Case strLevel
   Case "NATIONAL"
      iLeft = 35
      iWidth = 535
      iTop = 100
      iHeight = 130
      iFontSize = 9
   Case "REGION"
      If subNum = 1 Then
         iLeft = 5
         iWidth = 590
         iTop = 100
         iHeight = 90
         iFontSize = 9
      Else
         If (InStr(strTitle, "A") <> 0) Then
            iLeft = 35
            iTop = 274
         ElseIf (InStr(strTitle, "B") <> 0) Then
            iLeft = 170
            iTop = 274
         ElseIf (InStr(strTitle, "C") <> 0) Then
            iLeft = 305
            iTop = 274
         ElseIf (InStr(strTitle, "D") <> 0) Then
            iLeft = 440
            iTop = 274
         ElseIf (InStr(strTitle, "E") <> 0) Then
            iLeft = 35
            iTop = 363
         ElseIf (InStr(strTitle, "F") <> 0) Then
            iLeft = 170
            iTop = 363
         ElseIf (InStr(strTitle, "G") <> 0) Then
            iLeft = 305
            iTop = 363
         ElseIf (InStr(strTitle, "H") <> 0) Then
            iLeft = 440
            iTop = 363
         End If
         iWidth = 125
         iHeight = 70
         iFontSize = 7
      End If
   Case "DIRECTOR"
      iLeft = 5 + (200 * ((subNum - 1) Mod 3))
      iWidth = 195
      If (CInt((subNum - 1) / 3) - ((subNum - 1) / 3)) <= 0 Then
         iTop = 340 + (80 * CInt((subNum - 1) / 3))
      Else
         iTop = 340 + (80 * (CInt((subNum - 1) / 3) - 1))
      End If
      iHeight = 75
      iFontSize = 7
   End Select
   
   With xlSht.ChartObjects.Add(Left:=iLeft, Width:=iWidth, Top:=iTop, Height:=iHeight).Chart
      .SetSourceData Source:=rngData, PlotBy:=xlColumns
      .ChartType = xlCylinderBarStacked100
      .HasTitle = True
      .ChartTitle.Text = strTitle
      .ChartTitle.Font.Size = iFontSize
      .Axes(xlCategory).HasTitle = False
      .Axes(xlSeries).HasTitle = False
      .Axes(xlValue).HasTitle = False
      .HasAxis(xlCategory) = False
      .HasAxis(xlSeries) = False
      .HasAxis(xlValue) = False
      .Axes(xlCategory).CategoryType = xlAutomatic
      .PlotArea.Top = .ChartTitle.Top + 15
      .PlotArea.Height = .ChartArea.Height - .ChartTitle.Top - 20
      .PlotArea.Left = 10
      If (strLevel = "NATIONAL") Or ((strLevel = "REGION") And (subNum = 1)) Then
         .ChartArea.Border.LineStyle = xlLineStyleNone
         .HasLegend = True
         With .Legend
            Dim i As Integer
            .Position = xlLegendPositionRight
            For i = 1 To .LegendEntries.count
               .LegendEntries(i).Font.Size = 8
            Next
         End With
         If strLevel = "REGION" Then
            .PlotArea.Top = 1
            .PlotArea.Height = .Parent.Height - 10
            .PlotArea.Left = 1
            .PlotArea.Width = .Legend.Left - 1
         End If
         .HasTitle = False
      Else
         .PlotArea.Width = .ChartArea.Width - 20
         .HasLegend = False
      End If
      If ((strLevel = "REGION") And (subNum = 2)) Then
         .HasTitle = False
         .PlotArea.Left = 1
         .PlotArea.Width = .Parent.Width - 10
         .PlotArea.Top = 10
         .PlotArea.Height = .Parent.Height - 20
      End If
      .Parent.Placement = xlFreeFloating
   End With
   
   xlSht.Select

End Sub



Thank you,
Kriti
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Your code is full of sizing and chart positioning steps that I won't pretend to fully understand. If you created the code yourself then you need to walk through it and find where it is sizing your chart too small. If someone wrote it, then you can put some error trapping statements in it to see where it is causing your problems. Not having your data and chart available to work with, I am unable to help further. Anyone else able to help?
 

kritimehrotra

New Member
Joined
Jul 18, 2006
Messages
7
Hi Datsmart,

That's the reason I didn't post the code initially. The problem is not that one particular chart gets sized small each time. The problem is that it happens almost at random. The same chart in a different execution is fine. That's why I didn't think it was a problem with the code, but maybe just a general property of charts that I needed to make static or something.

Thanks,
Kriti
 

Forum statistics

Threads
1,136,878
Messages
5,678,306
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top