Add shape in ActiveCell from different worksheet

Question

I'm trying to add a shape in the ActiveCell in the "Visualizer" worksheet by clicking a button in the "Automater" Worksheet. While Excel is adding the shape in the "Visualizer" worksheet, it's adding it in the "Automater" worksheet ActiveCell's adress.

This is the code:

Sub button0_Click()
    Set visualizer = Sheets("Visualizer")
    Set shpOval = visualizer.Shapes.AddShape(msoShapeOval, ActiveCell.Left, ActiveCell.Top, 50, 50)
End Sub

score 0 · Answer 1 · answered Jul 25 '22 at 12:22

You need to qualify your ranges. visualizer is not inherited, so you would need to capture in each range, e.g.:

visualizer.range(visualizer.cells(1,1),visualizer.cells(2,2))

This can be simplified with a With statement, allowing changes to be made in one place for the sheet:

With visualizer
    .range(.cells(1,1),.cells(2,2))
End with

Regarding ActiveCell and sheet specifying, that doesn't work. The currently active sheet would need to be assessed for the particular instance/view of Excel's active cell.

As such, you need to activate the correct sheet for the specific shape location, you would want to capture your target cell's information, e.g.:

with visualizer
    .activate
    dim targetRow as long, targetCol as long
    targetRow = activecell.row
    targetCol = activecell.column
    With .Cells(targetRow,targetCol)    
        Set shpOval = visualizer.Shapes.AddShape(msoShapeOval, .Left, .Top, 50, 50)
    End With
end with

Please keep in mind, .Activate and .Select are typically avoided, so wherever you can specify the actual cell value, please do so. See: How to avoid using Select in Excel VBA

Add shape in ActiveCell from different worksheet

1 Answers1