Excel VBA How to change Shape properties in a different Sheet.

Question

I have a workbook where Sheet1 has a number of Shape objects. The Properties of some of these are changed by a sub that is invoked from OnTime. If Sheet1 is the active sheet the sub runs correctly. If another Sheet is active when the Sub executes, my code throws an error. Below is an example of the problem code:

Sheet1.Shapes.Range(Array("Rounded Rectangle 22")).Select

With Selection.ShapeRange.Fill 

    .Visible = msoTrue 

    .ForeColor.RGB = RGB(255, 124, 128) ' red 

    .Transparency = 0 

    .Solid 

End With

The error occurs on the With statement.

I have a temporary workaround whereby I activate Sheet1 at the start of my Sub and then return to the previously active sheet on completion. It works, but I'm not sure that is the best solution.

Is there a way to code this so it will work when a Sheet other than Sheet1 is active?

Answer 1

Select and Selection only work on the active sheet, so you have to avoid selecting cells.

    With Sheet1.Shapes("Rounded Rectangle 22").Fill 
        .Visible = msoTrue 
        .ForeColor.RGB = RGB(255, 124, 128) ' red 
        .Transparency = 0 
        .Solid 
    End With

Answer 2

Yes, that works. Thanks!