How to remove the 'Select' from this?

[roc_on_the_rocks]

Folks,

I'm cleaning up a long VBA code and I got really stuck here:

'   Adds AutoShape box for COMMENTS
    ActiveChart.Shapes.AddShape(msoShapeFoldedCorner, 432, 467, 240, 19).Select
        Selection.ShapeRange.Line.Weight = 1
        Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
        Selection.ShapeRange.Fill.Visible = msoTrue
        Selection.ShapeRange.Fill.Solid
        Selection.Characters.Text = "COMMENTS"
        Selection.Characters.Font.ColorIndex = xlAutomatic
        Selection.VerticalAlignment = xlCenter
        Selection.HorizontalAlignment = xlCenter

How can I get to remove the Select/Selection here?

Many thanks!

 

[mvptomlinson]

Perhaps:

'   Adds AutoShape box for COMMENTS
    With ActiveChart.Shapes.AddShape(msoShapeFoldedCorner, 432, 467, 240, 19)
        .ShapeRange.Line.Weight = 1
        .ShapeRange.Fill.ForeColor.SchemeColor = 13
        .ShapeRange.Fill.Visible = msoTrue
        .ShapeRange.Fill.Solid
        .Characters.Text = "COMMENTS"
        .Characters.Font.ColorIndex = xlAutomatic
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlCenter
    End With

 

[roc_on_the_rocks]

Thanks mvptomlinson. I've tried that.

It crashes at:

      .ShapeRange.Line.Weight = 1

I thought this would be trivial, but I'm stuck on this thing for the last couple hours...

Excel 2007

 

[roc_on_the_rocks]

Is the .ShapeRange really needed here? This may be part of the problem.

Any hint will be much appreciated.

 

[Norie]

Rewriting code like this can be particularly difficult, the first thing to do is find out exactly what it does.

Then replace/alter things step by step right from the start.

I'm only guessing but does the code start by creating a chart?

If it does that's where to begin, perhaps by creating a reference to the chart that gets created.

You could then use that reference in place of ActiveChart throughout the code.

 

[Norie]

I had a go myself.

Here's a recorded macro of me creating a chart and adding a shape with the text 'Comment' in it.

Option Explicit
Sub RecordedMakeChart()
'
' MakeChart Macro
'
'
    Range("A1:B6").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$6")
    ActiveChart.Shapes.AddShape(msoShapeFoldedCorner, 206, 2, 120.75, 29.25).Select
    ActiveChart.PlotArea.Select
    ActiveChart.Shapes.Range(Array("Folded Corner 1")).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Comment" & Chr(13) & ""
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 8).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 8).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Range("P4").Select
End Sub

Here's my slightly modified version.

Sub MyMakeChart()
Dim rng As Range
Dim cht As Shape
Dim cmt As Shape
 
    Set rng = Worksheets(1).Range("A1:B6")
    
    Set cht = Worksheets(1).Shapes.AddChart
    
    With cht.Chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=rng
    End With
    
    Set cmt = cht.Chart.Shapes.AddShape(msoShapeFoldedCorner, 206, 2, 120.75, 29.25)
    
    cmt.OLEFormat.Object.Text = "Comment"
    
End Sub

I suppose I sort of cheated and took some educated guesses, but I still used the syntax from the recorded code as a sort of 'guide'.

I'll see if I can perhaps redo the code more appropriately.

 

[roc_on_the_rocks]

Norie,

The purpose of this piece of VBA code is simply add to an existing Excel chart 'rectangular shape' containing some text (COMMENT). These are multiple charts, each one containing many of these 'comment boxes', with different messages.

My original code works fine, but whenever possible I try to tidy it up, which usually starts be removing the .Select/.Selections.

It may not be worth it if requires re-writing the code. Nevertheless, your help is valued and appreciated.

Thank you!

 

[Norie]

Well all you need to do is remove the part of my code that creates the chart.

This will loop through all the charts on a worksheet, check if they already have a shape, if they do change the text, if they don't add one.

Option Explicit
Sub MyMakeChart()
Dim ws As Worksheet
Dim cht As ChartObject
Dim cmt As Shape
 
    Set ws = Worksheets(1)
 
    For Each cht In ws.ChartObjects
        Select Case cht.Chart.Shapes.Count
            Case 0

                Set cmt = cht.Chart.Shapes.AddShape(msoShapeFoldedCorner, 206, 2, 120.75, 29.25)
                cmt.OLEFormat.Object.Text = "Comment"

            Case 1

                Set cmt = cht.Chart.Shapes(1)
                cmt.OLEFormat.Object.Text = "Commment 2"
 
        End Select
 
    Next cht
 
End Sub

 

[roc_on_the_rocks]

Norie, I like that! Very helpful, it has many uses on my scripts.

Many thanks!