Excel VBA / Adding values to ComboBox through variables and loops

[Young Grasshopper]

Hi Guys,

I have a group of shapes, that includes 15 texboxes named HeadlinePreview1, HeadlinePreview2, HeadlinePreview3 etc.
I would like to loop through these and ad the text to a combobox if it's not empty. It should be a simple matter, but i can't seem to make it work...

This is the code i have now.. What am I missing? (MyShp is a public variables and is tested, so no problems with that)

Private Sub HeadLine1Box_Click()
Dim ih As Long
Dim HeadlineX As String

HeadLine1Box.Clear

For ih = 1 To 15
If MyShp.GroupItems("HeadlinePreview" & ih).TextFrame.Characters.Text = "*" Then
HeadlineX = MyShp.GroupItems("HeadlinePreview" & ih).TextFrame.Characters.Text
Me.HeadLine1Box.AddItem HeadlineX
End If
Next

End Sub

Thank you

 

[Wookiee]

I believe the issue is that VBA doesn't like you combining text values to reference objects like that. I suggest creating an array which contains all the names you want to search for (which can be done using a loop to combine the text and numeric portions). Then, as you loop through the shapes on your sheet, have it evaluate whether the name of the shape appears in the array.

I put together this code and the function to check whether a value is in an array. You'll need to tweak it because I couldn't find some of the shape properties you had listed when I did my testing, but I believe this should at least get you to the point where you can identify and work with only those specific shapes.

Public Function f_IsInArray _
  (sSearch As String, vArray As Variant) As Boolean
'Crafted by Wookiee at MrExcel, but based heavily on codes found below:
'https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba

Dim lngLoop  As Long

For lngLoop = LBound(vArray) To UBound(vArray)

  If vArray(lngLoop) = sSearch Then

    f_IsInArray = True: Exit Function

  End If

Next lngLoop

End Function



Sub YoungGrass()

Dim lngIH         As Long
Dim HeadlineX     As String
Dim strName       As String
Dim arrNames(15)  As Variant

'Create Array Of Shape Names
For lngIH = 1 To 15

  arrNames(lngIH) = "HeadlinePreview" & lngIH

Next lngIH

On Error GoTo Bed

With ActiveSheet

   For Each myShp In .Shapes
   
      'Evaluate Whether Shape Name Appears In Array
      strName = myShp.Name

      If f_IsInArray(strName, arrNames) Then

        'do your thing with your shapes here, young grasshopper!

      End If
   
   Next myShp
   
End With


Bed:
On Error GoTo 0


End Sub