Finding Multiple Shapes

[gtd526]

I can't find the 2nd or 3rd Shape I'm want to edit.
It locates the 1st one, but not the 2nd or 3rd.
I used "record macro" to select the shapes and get the name.

Sub PleaseFind()

ActiveSheet.Shapes("Rectangle 9").Select

ActiveSheet.Shapes("Rectangle 5").Select

ActiveSheet.Shapes("Rectangle 6").Select

End Sub



The actual code I'm using:

Dim Result As Integer
Dim Result2 As Integer

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''find Min and Max Values for Shape1

Result = Application.WorksheetFunction.Max(Range("a2:a20"))
MsgBox Result
Result2 = Application.WorksheetFunction.Min(Range("a2:a20"))
MsgBox Result2

''apply Min and Max Value to 1st Shape
With ActiveSheet.Shapes("Rectangle 9")
.Height = Result
.Width = 72 * 1.59
End With

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''find Min and Max Values for Shape2

Result = Application.WorksheetFunction.Max(Range("a21:a79"))
'MsgBox Result
Result2 = Application.WorksheetFunction.Min(Range("a21:a79"))
'MsgBox Result2

''apply Min and Max Value to 2nd Shape
''''''''''''''''''not finding Rectangle 5''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With ActiveSheet.Shapes("Rectangle 5")
.Height = Result
.Width = 72 * 5.54
End With

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''find Min and Max Values for Shape3

Result = Application.WorksheetFunction.Max(Range("a80:a88"))
'MsgBox Result
Result2 = Application.WorksheetFunction.Min(Range("a80:a88"))
'MsgBox Result2

''apply Min and Max Value to 3rd Shape
''''''''''''''not finding Rectangle 6'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With ActiveSheet.Shapes("Rectangle 6")
.Height = Result
.Width = 72 * 1.62
End With

Once I can find the 2nd and 3rd shape, I will ask how to set the height using Top and Bottom Values for the shape and the width according to columns in the chart. I'm using shapes to go over a Column Chart to define the Range of Min and Max values according to several columns in the chart.

Thanks for your help.

 

[JoeMo]

This will give you the cell address of the upper left corner of each rectangle, whether or not the shape is visible.Change shape names to suit.

Sub FindMyShapes()
With ActiveSheet
   MsgBox .Shapes("Rectangle 1").TopLeftCell.Address
   MsgBox .Shapes("Rectangle 2").TopLeftCell.Address
   MsgBox .Shapes("Rectangle 3").TopLeftCell.Address
End With
   
End Sub

 

[gtd526]

Thanks for your help.
I used your code and it was only finding the first one.
I kept digging, so I deleted the first shape and copied the second one, it was finding this one.
Now, it finds all shapes I'm asking to select
The copy/paste changed the name and retained all the "properties" of the second one, it worked.
I used the following code to fix:

Sub fixit()

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Shapes("Rectangle 8").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Shapes("Rectangle 5").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Shapes("Rectangle 6").Select


End Sub

I think 'properties' of the shape had something to do with the error????? I'm only assuming, but now it works.

I will experiment with the size and position of the rectangle shapes (overlapping my column chart).
I get into 'investigative mode' when something doesn't work.

Thanks for your help.
P.S. I'm not 19 anymore, everyone forgets, it not a crime. With age comes wisdom, I think

 

[Yongle]

With age comes wisdom

a statement requiring a raft of exceptions to accompany it