Share via

Specified name not found

Anonymous
2011-02-03T01:04:14+00:00

Following is my code.  I generate a chart (no problem), but then I want to move it.  I get the error "The item with the specified name wasn't found".  I have tried dozens of variations, but I need the line(s) of code that allow me to move and resize the chart on the same sheet as the data.  TIA  Bill Roberts

Public FirstCell As Object

Public a As Long

Public b As Long

Public rngsource As Range

Public rng1 As Range

Public rng2 As Range

Public rng3 As Range

Sub Chart()

Cells(maxdays - 40, "B").Select

Set FirstCell = ActiveCell

Set rng1 = Range(FirstCell.Offset(0, 0), FirstCell.Offset(42, 0))

Set rng2 = Range(FirstCell.Offset(0, 7), FirstCell.Offset(42, 7))

Set rng3 = Range(FirstCell.Offset(0, 8), FirstCell.Offset(42, 8))

ActiveSheet.Shapes.AddChart.Select

Set rngsource = Union(rng1, rng2, rng3)

ActiveChart.SetSourceData Source:=rngsource, PlotBy:=xlColumns

ActiveChart.SeriesCollection(2).Select

ActiveChart.SeriesCollection(2).AxisGroup = 2

With ActiveChart

    .HasLegend = False

    .Axes(xlCategory).HasMajorGridlines = True

    .Axes(xlValue, xlPrimary).HasMajorGridlines = False

    .Axes(xlValue, xlSecondary).HasMajorGridlines = True

    .SeriesCollection(1).Select

        With Selection

            .MarkerStyle = 1

            .MarkerSize = 4

        End With

    .SeriesCollection(2).Select

        With Selection

            .MarkerStyle = 3

            .MarkerSize = 4

        End With

End With

'NOTE: This is the error line******************

ActiveSheet.Shapes("Chart 1").Activate

ActiveSheet.Shapes("Chart 1").Select

ActiveSheet.Shapes("Chart 1").IncrementLeft 229.5

ActiveSheet.Shapes("Chart 1").IncrementTop -140.25

ActiveWindow.SmallScroll ToRight:=10

ActiveSheet.Shapes("Chart 1").ScaleWidth 1.7343751094, _

                                msoFalse, msoScaleFromTopLeft

ActiveSheet.Shapes("Chart 1").ScaleHeight 1.9670140712, _

                                msoFalse, msoScaleFromTopLeft

End Sub

Bill Roberts

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-02-05T01:25:44+00:00

The following code is correct.  It does create a minor problem. If the chart exists, I want to delete it.  As far as the chart postion and sizing goes, it is good.  I found this code on a posting from Bill Manville dated September 15, 2010.  He should get credit for the solution.  Thanks to all.  Bill Roberts

Public FirstCell As Object

Public rngsource As Range

Public rng1 As Range

Public rng2 As Range

Public rng3 As Range

Sub Chart()

Sheets("MASTER").Select

Cells(maxdays - 60, "B").Select

Set FirstCell = ActiveCell

Set rng1 = Range(FirstCell.Offset(0, 0), FirstCell.Offset(62, 0))

Set rng2 = Range(FirstCell.Offset(0, 7), FirstCell.Offset(62, 7))

Set rng3 = Range(FirstCell.Offset(0, 8), FirstCell.Offset(62, 8))

ActiveSheet.Shapes.AddChart.Select

Set rngsource = Union(rng1, rng2, rng3)

ActiveChart.SetSourceData Source:=rngsource, PlotBy:=xlColumns

ActiveChart.SeriesCollection(2).Select

ActiveChart.SeriesCollection(2).AxisGroup = 2

With ActiveChart

    .HasLegend = False

    .Axes(xlCategory).HasMajorGridlines = True

    .Axes(xlValue, xlPrimary).HasMajorGridlines = False

    .Axes(xlValue, xlSecondary).HasMajorGridlines = True

    .SeriesCollection(1).Select

        With Selection

            .MarkerStyle = 1

            .MarkerSize = 4

        End With

    .SeriesCollection(2).Select

        With Selection

            .MarkerSize = 4

            .MarkerStyle = 3

        End With

     .ChartArea.Select

End With

With ActiveChart.Parent.ShapeRange  'This is the critical line and the code Bill Manville suggested

 .IncrementLeft 100

.IncrementTop 250

.ScaleWidth 1.3, msoFalse, msoScaleFromTopLeft

.ScaleHeight 1.4, msoFalse, msoScaleFromTopLeft

ActiveWindow.SmallScroll ToRight:=5

End With

End Sub

Bill Roberts

Was this answer helpful?

0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-02-03T19:08:02+00:00

    Unfortunately, I can't get any of the suggestions to work.  The name of the chart is Chart.  In my debugging process, I delete the chart, change the code, and rerun to see if I can get past the error message.  If it's any help, when I "record macro" and step throught the code, the first run is "Chart 1", and it increments every time (e.g., "Chart 15"), even though I delete the chart every time before I run the code again.  Does this offer any clues?  Thanks.  Bill Roberts.

    Public FirstCell As Object

    Public a As Long

    Public b As Long

    Public rngsource As Range

    Public rng1 As Range

    Public rng2 As Range

    Public rng3 As Range

    Dim mychart As Shape

    Dim ChartName As String

    Sub Chart()

    Cells(maxdays - 60, "B").Select

    Set FirstCell = ActiveCell

    Set rng1 = Range(FirstCell.Offset(0, 0), FirstCell.Offset(62, 0))

    Set rng2 = Range(FirstCell.Offset(0, 7), FirstCell.Offset(62, 7))

    Set rng3 = Range(FirstCell.Offset(0, 8), FirstCell.Offset(62, 8))

    ActiveSheet.Shapes.AddChart.Select

    Set rngsource = Union(rng1, rng2, rng3)

    ActiveChart.SetSourceData Source:=rngsource, PlotBy:=xlColumns

    ActiveChart.SeriesCollection(2).Select

    ActiveChart.SeriesCollection(2).AxisGroup = 2

    With ActiveChart

        .HasLegend = False

        .Axes(xlCategory).HasMajorGridlines = True

        .Axes(xlValue, xlPrimary).HasMajorGridlines = False

        .Axes(xlValue, xlSecondary).HasMajorGridlines = True

        .SeriesCollection(1).Select

            With Selection

                .MarkerStyle = 1

                .MarkerSize = 4

            End With

        .SeriesCollection(2).Select

            With Selection

                .MarkerSize = 4

                .MarkerStyle = 3

            End With

         .ChartArea.Select  ' I Added this line to deslect seriescollection(2)

    End With

             'NOTE: This is the error line. Tried various combinations of mychart,

             'chart, chart 1, chartname (with & without quotes), and I always get an error)

             'I tried this code (separately), but no luck.

    'Set mychart = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

    'ChartName = ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Name

    Call whatsinaname                           'The messagebox returns Chart

    ActiveSheet.Shapes("Chart 1").Activate

    ActiveSheet.Shapes("Chart 1").Select

    ActiveSheet.Shapes("Chart 1").IncrementLeft 229.5

    ActiveSheet.Shapes("Chart 1").IncrementTop -140.25

    ActiveWindow.SmallScroll ToRight:=10

    ActiveSheet.Shapes("mychart").ScaleWidth 1.7343751094, _

                                    msoFalse, msoScaleFromTopLeft

    ActiveSheet.Shapes("mychart").ScaleHeight 1.9670140712, _

                                    msoFalse, msoScaleFromTopLeft

    End Sub

    Sub whatsinaname()   'Runs OK.  Returns Chart.  But it isn't correct.  It's "Chart n".

    MsgBox Selection.Name

    End Sub

    Bill Roberts

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-02-03T04:09:38+00:00

    A great idea!  Rather than "discover" the Name, name it yourself!

    gsnu201101

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-02-03T01:59:38+00:00

    Stop your code just before the error and then click in the chart and run:

    Sub WhatsInAName()

    MsgBox Selection.Name

    End Sub

    This will display the Name of the chart in question.  Use the true Name in place of "Chart 1"

    I don't do any work with charts, so naming may be different for them, but if they are really just shapes, then the number attached to the word Chart in its name will change as the number of shapes in the workbook changes, so your method may only work once for the OP, but not for the users of his workbook. There are two ways I see that the OP can proceed... one, Dim a variable as a Shape and set the newly created Chart to this variable; for example, like this...

    Dim MyChart As Shape

    Set MyChart = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

    then he will have an object reference to the chart that he can use in the rest of his code; or, two, Dim a string variable and make this assignment...

    Dim ChartName As String

    ChartName = ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Name

    to this variable immediately after creating the chart, then he can use that String variable's name as the argument (no quote marks around it) wherever he now has "Chart 1".

    NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-02-03T01:24:54+00:00

    Stop your code just before the error and then click in the chart and run:

    Sub WhatsInAName()

    MsgBox Selection.Name

    End Sub

    This will display the Name of the chart in question.  Use the true Name in place of "Chart 1"

    gsnu201101

    Was this answer helpful?

    0 comments