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

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
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)?
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Assign the following macro to all shapes

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

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top