Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert shape without SELECT

Tags:

excel

vba

A piece of my code goes through a range of cells and if some cell satisfies certain criteria - inserts the shape in this cell. It works, but I would like to find out an alternative approach avoiding select.

'above - code to find satisfying cell
ActWS.Activate       'Activate Sheet
ActWS.Cells(rActPlan - 1, vReturnColumn).Select 'Select satisfying cell
ActiveSheet.Shapes.AddShape(msoShapeOval, ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height).Select
Selection.ShapeRange.Fill.Visible = msoFalse
 With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 255, 0)
        .Weight = 2.25
 End With
like image 988
Meursault Avatar asked Feb 08 '23 23:02

Meursault


1 Answers

This code removes all .Select and ActiveCell references:

With ActWs

    Dim rng as Range
    Set rng = .Cells(rActPlan - 1, vReturnColumn)

    Dim shp as Shape
    Set shp = .Shapes.AddShape(msoShapeOval, rng.Left, rng.Top, rng.Width, rng.Height)

    With shp.ShapeRange

        .Fill.Visible = msoFalse

        With .Line
           .Visible = msoTrue
           .ForeColor.RGB = RGB(0, 255, 0)
           .Transparency = 0
           .Weight = 2.25
        End With

    End With

End With
like image 61
Scott Holtzman Avatar answered Feb 18 '23 07:02

Scott Holtzman