Can't change the format of a Shapes collection

[Torquemada]

I have 20 shapes on Sheet2 and I want to format them all identically without looping. I put the following in a Workbook:

Public sr As ShapeRange
Private Sub Workbook_Open()
Sheets("Sheet2").Shapes.SelectAll
Set sr = Selection.ShapeRange
sr.Fill.ForeColor.SchemeColor = 12
End Sub

This works fine. Now I go to Sheet2 and try to change all the shape colors to something else with this code in a module:

Public sr As ShapeRange
Sub ChangeColors()
sr.Fill.ForeColor.SchemeColor = 13
End Sub

This won't run. What am I overlooking?

 

[scifibum]

I think the problem is your declaration of the sr variable.

Workbook and worksheet code modules can't contain a truly "Public" variable. Those need to reside in regular code modules. Plus, you should only declare a public variable once - it looks like you've declared it in both the workbook and worksheet modules.

The upshot is that your worksheet code can't access the variable you initialized in the workbook routine. It's accessing the local version of sr which is not initialized.

Try removing the "Public sr as ShapeRange" declaration from both places and put it in a regular code module, and see if that solves the problem.

Hope this helps.

 

[Torquemada]

scifibum: Your suggestion solved the problem. Thank you.