Shapes Array (Excel 2013)

[davis1118]

Hello,
I am in the process of trying to create multiple shapes (rounded rectangles) and place them on the worksheet. I have been able to create and place multiple shapes, but I am having trouble with creating the names and text for the shapes. The code I have below was copied from code that I was using to create multiple form control command buttons, which worked fine for those. The reason I am tying to use shapes is because I was going to use the shape as a "button" to hyperlink to external documents. The shape seems easier to insert a hyperlink directly into rather than creating a Sub that all the buttons need to point to( I haven't got to adding the hyperlink code yet, but I should be able to figure that out). The code errors on the .Name = arrNames line with "Type mismatch".

Thank you for the help.

Sub AddButtons()
Dim arrNames            As Variant
Dim arrCaptions         As Variant
Dim LeftPos             As Long
Dim Gap                 As Long
Dim i                   As Long
Dim newBtn              As Shape


shtnm = ThisWorkbook.ActiveSheet.Name
Application.ScreenUpdating = False
   
   With ThisWorkbook.Sheets(shtnm)
        arrNames = Array("EngBtn1", "EngBtn2")
       
        arrCaptions = Array(ENGForm.DocName1.Value, ENGForm.DocName2.Value)
        
        LeftPos = 165
        
        Gap = 9.75
        
        For i = 1 To 2
            Set newBtn = .Shapes.AddShape(msoShapeRoundedRectangle, Left:=LeftPos, Top:=225.5, Width:=105.75, Height:=52.5)
           
            With newBtn
                .Name = arrNames
                .TextFrame.Characters.Text = arrCaptions
                .Fill.ForeColor.RGB = RGB(136, 182, 224)
            End With
            
            LeftPos = LeftPos + newBtn.Width + Gap
            
         Next i
         
        ActiveWindow.Zoom = 98
    End With
Application.ScreenUpdating = True
End Sub

[p45cal]

You're trying to assign a whole array of names to one shape.
Try something along the lines of:

  For i = LBound(arrNames) To UBound(arrNames) 'this means it doesn't matter whether the array is zero- or one-based
    Set newBtn = .Shapes.AddShape(msoShapeRoundedRectangle, Left:=LeftPos, Top:=225.5, Width:=105.75, Height:=52.5)
    With newBtn
      .Name = arrNames(i)
      .TextFrame.Characters.Text = arrCaptions(i)
      .Fill.ForeColor.RGB = RGB(136, 182, 224)
    End With
    LeftPos = LeftPos + newBtn.Width + Gap
  Next i

[davis1118]

Perfect, it finally works! Thank you very much for the help, I'm very glad this works now. I'm curious as to why the "For i = 1 To 2" worked for form controls but not Shapes.

Thanks again!

[p45cal]

I'm curious as to why the "For i = 1 To 2" worked for form controls but not Shapes.

It's not whether form controls or shapes are involved; it's what you do with the value of i in the ensuing code.
If you use i to refer to a member of an arrray, you have to be sure that that member exists. In the case of For i = 1 To 2 the following have to exist:
arrNames(1)
arrCaptions(1)
arrNames(2)
arrCaptions(2)
Whether they exist or not depends on how the arrays have been set up.
They could have been set up like:
2018-03-27_100834.jpg
or:
2018-03-27_101025.jpg
The first will work, the second won't.
If, instead of hard-coding 1 To 2, you use LBound(arrNames) to UBound(arrNames) you don't need to worry about whether the arrays are 1-based or zero-based. Additionally, should you set up arrNames with more names:
arrNames = Array("EngBtn1", "EngBtn2", "EngBtn3", "EngBtn4", "EngBtn5")
(and you do something similar with arrCaptions) you won't need to adjust the For i = … code.

[davis1118]

Thank you very much p45cal! I appreciate the help with my problem and taking the time to better explain the array to me. I like the Lbound and Ubound function, it seems like a much more straight forward way to use the array. Thanks again!