You might be working with maps or shapes in Excel that you need to have specific descriptors depending on a particular event. The macro below shows how to set this up in an Excel workbook.
First, create your shape/map.
Next, create a text box that will operate as a label for your shape/map, and give this text box a name by typing a specific name in the Name Box. In this example the name I’m using is MyLabel.
Format the text box by removing the fill colour, outline, and enhancing the font so it looks like an actual label.
Next, earmark a cell that will contain the name of the caption or label that will be in this dynamic label. In this example this will be C1 – it returns the year selected in this model:
Then insert a new Module in Visual Basic and bring in the following code:
Sub UpdateLabel()
Dim aLabel As Shape
Set aLabel = ActiveSheet.Shapes(“MyLabel”)
LabelSel = Range(“C1”).Value
aLabel.TextFrame2.TextRange.Characters.Text = LabelSel
End Sub
{In a previous version that worked as well, I included a line after the first line to declare the range variable (“Dim LabelSel as Range”). If you get a Excel runtime error 91 – Object or with block variable not set, go ahead to include this line}
Assign this macro to a button to run when the button is clicked, or set it up to change on the event cell C1 changes, and voila! all done!
