none
How to group all the shapes I created in Excel?

    Question

  • Hi there,

    I'm using VS 2015 and Excel 2016 to develop my Addin.

    In the below function, I've created a button to add the shapes in Excel depending on how user select the cell.

    Private Sub MultiIllu_Click(sender As Object, e As RibbonControlEventArgs) Handles MultiIllu.Click Dim app As Excel.Application = Globals.ThisAddIn.Application Dim shp As Excel.Shape Dim sht As Excel.Worksheet = app.ActiveWorkbook.ActiveSheet Dim arrow As Excel.Shape Dim connectorref As Excel.Shape = Nothing Dim r As Excel.Range Dim i As Integer = Nothing Dim selection As String = app.Selection.Address(ReferenceStyle:=Excel.XlReferenceStyle.xlA1, RowAbsolute:=False, ColumnAbsolute:=False) r = app.Range(selection) shp = app.ActiveSheet.Shapes.Addconnector _ (Microsoft.Office.Core.MsoConnectorType.msoConnectorStraight, r.Left + r.Width + 8, r.Top + 10, r.Left + r.Width + 8, r.Height + r.Top + 8) shp.Line.ForeColor.RGB = RGB(255, 0, 0) arrow = app.ActiveSheet.Shapes.Addconnector _ (Microsoft.Office.Core.MsoConnectorType.msoConnectorStraight, r.Left + r.Width + 8, r.Height + r.Top + 8, r.Left + r.Width + 22, r.Top + r.Height + 8) arrow.Line.ForeColor.RGB = RGB(255, 0, 0) arrow.Line.EndArrowheadStyle = Microsoft.Office.Core.MsoArrowheadStyle.msoArrowheadTriangle For i = 0 To (r.Height / 16.5 - 1) connectorref = app.ActiveSheet.Shapes.Addconnector _ (Microsoft.Office.Core.MsoConnectorType.msoConnectorStraight, r.Left + r.Width, r.Top + 10 + i * 16.5, r.Left + r.Width + 8, r.Top + 10 + i * 16.5) connectorref.Line.ForeColor.RGB = RGB(255, 0, 0)

    connectorref.Name = "connectorref" & i Next 'Group shps all together Dim MidObj As Object() = New Object() {shp.Name, arrow.Name, connectorref.Name} app.ActiveSheet.Shapes.Range(MidObj).Group() End Sub

    I want to group all the shapes I created. I think I can use loop like do until to achieve that, but somehow I stuck here.

    How should I do to add the connectorref.Name programmatically depending on the variable i?

    Dim MidObj As Object() = New Object() {shp.Name, arrow.Name,

    (connectorref0, connectorref1, to .... connectorref(r.Height / 16.5 - 1))}

    Please help me!

    Any suggestion would be appreciated. Thanks a lot in advance.



    Sunday, March 19, 2017 7:38 AM

Answers

  • Hi AuditorQQ,

    According to your description, i think you need a way to add shapes into the object array dynamically. Redim Preserve would be useful to do this.

    Here is the example

            Dim MidObj() As Object
            ReDim Preserve MidObj(2)
            MidObj(0) = shp.Name
            MidObj(1) = arrow.Name
            For i = 0 To (r.Height / 16.5 - 1)
                connectorref = app.ActiveSheet.Shapes.Addconnector _
    (Microsoft.Office.Core.MsoConnectorType.msoConnectorStraight, r.Left + r.Width, r.Top + 10 + i * 16.5, r.Left + r.Width + 8, r.Top + 10 + i * 16.5)
                connectorref.Line.ForeColor.RGB = RGB(255, 0, 0)
                connectorref.Name = "connectorref" & i
                ReDim Preserve MidObj(i + 2)
                MidObj(i + 2) = connectorref.Name
            Next

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AuditorQQ Monday, March 20, 2017 9:36 AM
    Monday, March 20, 2017 5:36 AM
    Moderator

All replies

  • Hi AuditorQQ,

    According to your description, i think you need a way to add shapes into the object array dynamically. Redim Preserve would be useful to do this.

    Here is the example

            Dim MidObj() As Object
            ReDim Preserve MidObj(2)
            MidObj(0) = shp.Name
            MidObj(1) = arrow.Name
            For i = 0 To (r.Height / 16.5 - 1)
                connectorref = app.ActiveSheet.Shapes.Addconnector _
    (Microsoft.Office.Core.MsoConnectorType.msoConnectorStraight, r.Left + r.Width, r.Top + 10 + i * 16.5, r.Left + r.Width + 8, r.Top + 10 + i * 16.5)
                connectorref.Line.ForeColor.RGB = RGB(255, 0, 0)
                connectorref.Name = "connectorref" & i
                ReDim Preserve MidObj(i + 2)
                MidObj(i + 2) = connectorref.Name
            Next

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AuditorQQ Monday, March 20, 2017 9:36 AM
    Monday, March 20, 2017 5:36 AM
    Moderator
  • Hi Celeste Li

    I didn't know the ReDim Preserve syntax before. Thank you so much at first!

    But there's another problem happened, the first time I press the button, the function goes well, all shapes are grouped.

    When I press the button again also the third  time and the fourth time ..., the line generated by code are not grouped with the shp and the arrow.  Do you know how to fix this problem?

    Best regards, 

    AuditorQQ

    Monday, March 20, 2017 9:56 AM
  • Hi AuditorQQ,

    It fails because we have used line connectorref.Name = "connectorref" & I to specify the names of shapes, please comment out this line and the code would work fine.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 1:43 AM
    Moderator