Positioning Shapes based off another Shape

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
46
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Morning All

I have a worksheet with multiple Shapes. These Shapes are named "Cause1", "Cause2", Cause3" etc. The number of these Shapes is based on the number of Rows of Data present in Column C. I have other Shapes but with different names based on data from other Columns I am trying to select all of the Shapes that contain the name "Cause" so that I can Group them to see the Total Height of these Shapes and the Gaps in between them. I will then Use the Centre point of this group as the Top position of another Shape. The Code I am trying to get to work is below, but does not seem to work.

VBA Code:
NumOfCauses = ActiveSheet.Range("C2").End(xlDown).Row - 1

    For i = 1 To NumOfCauses
        
   For Each Sh In ActiveSheet.Shapes
    If Sh.Name = "Cause" & i Then Sh.Select True
  Next
  With Selection
  .Group

 End With
 Next i

End Sub

Any ideas what I am doing wrong or another way I should be thinking about it?

Thanks

Steve
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,371
Office Version
  1. 2013
Platform
  1. Windows
@stirlingmw1
Try this.

VBA Code:
Sub ShapeSel()
Dim ShpArry() As String
Dim i As Integer
NumOfCauses = ActiveSheet.Range("C2").End(xlDown).Row - 1
ReDim ShpArry(1 To NumOfCauses)
    For i = 1 To NumOfCauses
    ShpArry(i) = "Cause" & i
    Next i
    ActiveSheet.Shapes.Range(ShpArry).Select
   
  Selection.Group
End Sub

Hope that helps.
 
Solution

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
46
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
@stirlingmw1
Try this.

VBA Code:
Sub ShapeSel()
Dim ShpArry() As String
Dim i As Integer
NumOfCauses = ActiveSheet.Range("C2").End(xlDown).Row - 1
ReDim ShpArry(1 To NumOfCauses)
    For i = 1 To NumOfCauses
    ShpArry(i) = "Cause" & i
    Next i
    ActiveSheet.Shapes.Range(ShpArry).Select
  
  Selection.Group
End Sub

Hope that helps.
Tony

Thank you, works perfect.


Steve
 

Forum statistics

Threads
1,144,162
Messages
5,722,848
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top