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)?
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)?
VBA Code:
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