Changing text in a shape (dynamic shape name) based on combo box value

[wbstadeli]

Hi,

I have a named range that in column 5 of each row, upon clicking that cell, a combobox populates with a dropdown for the user to make a selection ran from worksheet selection change event. This all is working great. I have added a shape (rounded rectangle) over this cell for a better visual appeal, assigned a macro to this shape (to select the topleftcell of shape) which in turn actives the combobox dropdown for a users selection. This is all working great. The problem is that i want the rounded rectangle shape text to change be whatever the combobox text value is, without using the actual shape's name because this is a dynamic range. With each row added into the named range, i want to copy this shape into the new row (hence the shape's name will be different on each row, this would be done in a separate macro). The code below works great with the combobox's change event because i have the shapes actual name in there, but how do i get this line to work if the shape name is changing between rows (dynamic)?

Private Sub Material_Quoted_By_Combobox_Change()

    Me.Material_Quoted_By_Combobox.Visible = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 16").TextFrame.Characters.Text = Me.Material_Quoted_By_Combobox.Text
    activecell.Offset(, -2).Activate
   
End Sub

 

[DanteAmor]

Assign the following macro to all shapes

Sub Changing_text_in_a_shape()
  Dim v As Variant
  v = Application.Caller
  Me.Material_Quoted_By_Combobox.Visible = False
  ActiveSheet.Shapes(v).TextFrame.Characters.Text = Me.Material_Quoted_By_Combobox.Text
  ActiveCell.Offset(, -2).Activate
End Sub

 

[wbstadeli]

Thanks!

 

[DanteAmor]

Youre welcome!