chart Q

[Geordie]

I have 30 charts in a work book (graphs) how do I find out what they are named?

e.g. Chart1 / Chart2 etc?

M

 

[schielrn]

I believe if you right click on it there should be a selection for chart window. Check that and it should give you a bar at top with the name I believe. Not positive on this though, but I think I recall that.

 

[Datsmart]

Right-Click Chart and choose "Assign Macro"
Macro Name box will show Chart Number. i.e. Chart1_Click for Chart 1.

 

[Geordie]

Ah great - thanks guys.
M

 

[Datsmart]

If you want to build a list of the worksheets and chart names, this code will build it in column A and B of the active worksheet:

Sub ListChartNames()
Dim j As Integer
Dim NumSheets As Integer
Dim MyListSht
    MyListSht = ActiveSheet.Name
    NumSheets = Sheets.Count
    For j = 1 To NumSheets
        Sheets(j).Activate
            For Each ChObj In ActiveSheet.ChartObjects
                lr = Sheets(MyListSht).Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheets(MyListSht).Cells(lr, 1) = Sheets(j).Name
                Sheets(MyListSht).Cells(lr, 2) = ChObj.Name
            Next ChObj
    Next j
End Sub

Column A will have the WorkSheet name, Column B will have each Chart Name.

 

[Geordie]

Assigning a macro to a chart has given me another idea thow....!

Is it possible to set assign a macro to a chart so that when I click the graph a macro runs that does the following:

Updates the min and max value of the x axis by calculating the min and max of a specified dataset and applying that to the scale of the axis???

Thanks
M

 

[Geordie]

Assigning a macro to a chart has given me another idea thow....!

Is it possible to set assign a macro to a chart so that when I click the graph a macro runs that does the following:

Updates the min and max value of the x axis by calculating the min and max of a specified dataset and applying that to the scale of the axis???

Thanks
M

 

[Datsmart]

This earlier post has code that does what you are asking.
http://www.mrexcel.com/board2/viewtopic.php?p=1370090
Note the .MinimimScale and .MaximumScale = sections.

 

[Jon Peltier]

Here's a quick and dirty procedure that resets the Y axis of a chart, provided the chart is assigned this procedure. If the chart has a secondary Y axis, this will likely give strange limits, and if the Y axis is logarithmic it is also likely to give strange limits.

Sub FixYAxisLimits()
  Dim dMin As Double
  Dim dMax As Double
  Dim dSpan As Double
  Dim dLog As Double
  Dim dPre As Double
  Dim dMaj As Double
  Dim dUnit As Double
  Dim cht As Chart
  Dim iSrs As Long

  dMin = 1E+307
  dMax = -1E+307

  Set cht = ActiveSheet.ChartObjects(Application.Caller).Chart
  For iSrs = 1 To cht.SeriesCollection.Count
    With cht.SeriesCollection(iSrs)
      If dMin > WorksheetFunction.Min(.Values) Then
        dMin = WorksheetFunction.Min(.Values)
      End If
      If dMax < WorksheetFunction.Max(.Values) Then
        dMax = WorksheetFunction.Max(.Values)
      End If
    End With
  Next

  If dMax = dMin Then dMax = dMin + 1

  dSpan = dMax - dMin
  If dMin <> 0 Then dMin = dMin - dSpan / 100
  If dMax <> 0 Then dMax = dMax + dSpan / 100

  dLog = Int(Log(dSpan) / 2.303)
  dPre = dSpan / (10 ^ dLog)

  Select Case True
    Case dPre > 5
      dUnit = 1
    Case dPre > 2
      dUnit = 0.5
    Case dPre > 1
      dUnit = 0.2
    Case Else
      dUnit = 0.1
  End Select

  dMaj = dUnit * (10 ^ dLog)

  With cht.Axes(xlValue)
    If .MaximumScale < .MinimumScale = Int(dMin / dMaj) Then
      .MinimumScale = Int(dMin / dMaj) * dMaj
      .MaximumScale = Int(dMax / dMaj + 1) * dMaj
    Else
      .MaximumScale = Int(dMax / dMaj + 1) * dMaj
      .MinimumScale = Int(dMin / dMaj) * dMaj
    End If
    .MajorUnit = dMaj
  End With

End Sub