PDA

View Full Version : [SOLVED] Shapes Array (Excel 2013)



davis1118
03-26-2018, 02:00 PM
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
03-26-2018, 03:06 PM
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
03-26-2018, 06:40 PM
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
03-27-2018, 02:14 AM
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:
21929
or:
21930
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
03-27-2018, 04:27 PM
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!