Custom Properties or Tags for Shapes in Excel

[MutatedBrain]

Hello,
I was trying to program a Org Chart type of application where it
attempts to generate a chart based out of an Excel List. In the chart
I use shapes to show the person names and was wanting to work out a
way in which when the user clicked on the shape, it would pop up the
relevant details about that person (address, contact information,
etc.). I could do this in two ways,
1. Assign the name of the shape which is same as the name of the
person, so when the user clicks on the shape, the Name could be passed
to the form, which in turn would traverse down the list and pick out
the relevant information. Not very elegant.
2. Assign Custom Properties or Tags to each of the Shape so that its
easy to just refer to the shape and get the required information. But
with excel I was unable to work out the tags for the shape or get to
assign custom properties. What do you folks suggest ? Would a custom
class in which shape is object be a good idea ? Since the
application.caller would have the relevant name, i could write a
method to return the object containing the relevant shape ?
I am stuck for direction, I know method 1 is easy to implement, but
somehow I am not very comfortable in working with it. Any suggestions ?

 

[Chip Pearson]

If you are using the shapes from the Drawing command bar, you could assign
text to the AlternativeText property, which is generally used for
alternative text when displaying a web page, but can be used a general
storage. If you have multiple properties you need to store, create a string
with those property names and values, separated by a '|' character. For
example,


Sub SetAltText()
Dim SH As Shape
Set SH = ActiveSheet.Shapes("Rect1")
SH.AlternativeText = "Prop1Name=Prop1 Value|Prop2=Value2|Prop3=Value3"
End Sub

Sub GetAltTextProperties()
Dim SH As Shape
Dim Properties As Variant
Dim PropertyPairs As Variant
Dim PropertyPair As Variant
Dim PropName As String
Dim PropValue As String
Dim N As Long

Set SH = ActiveSheet.Shapes("Rect1")
Properties = SH.AlternativeText
PropertyPairs = Split(Properties, "|")
For N = LBound(PropertyPairs) To UBound(PropertyPairs)
PropertyPair = Split(PropertyPairs(N), "=")
PropName = PropertyPair(0)
PropValue = PropertyPair(1)
Debug.Print PropName, PropValue
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[MutatedBrain]

Thank you Mr. Pearson. Is there any limitations on alternative text ?
Is it limited to only 256 characters or something ?

 

[Chip Pearson]

Is it limited to only 256 characters or something ?

AlternativeText length is limited to 4095 characters in Excel 2003. It seems
to be unlimited in Excel 2007 (I broke out of the loop at 64K characters and
it was still going strong).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[MutatedBrain]

Hi,
Thanks for your help.I tried to determine the exact length. Here are
my observations:
1. The AlternativeText is limited to 255 characters via the user
interface. You can view / edit only the first 255 characters.
2. I was able to enter at least 4096 characters in AlternativeText via
VBA. I tested it with the following code. This seems to be a fair
amount of space for me for all pratical purposes.

Thanks Mr. Pearson

Dim MetaString As String

MetaString = ""
For i = 1 To 4095
MetaString = MetaString & "A"
ActiveSheet.Shapes("Box").AlternativeText = MetaString
Next i
MetaString = MetaString & "B"
ActiveSheet.Shapes("Box").AlternativeText = MetaString

Debug.Print Len(ActiveSheet.Shapes("Box").AlternativeText)
Debug.Print Right((ActiveSheet.Shapes("Box").AlternativeText), 1)