Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Properties or Tags for Shapes in Excel
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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Properties or Tags for Shapes in Excel
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" wrote in message oups.com... 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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Properties or Tags for Shapes in Excel
Thank you Mr. Pearson. Is there any limitations on alternative text ?
Is it limited to only 256 characters or something ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Properties or Tags for Shapes in Excel
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" wrote in message ps.com... Thank you Mr. Pearson. Is there any limitations on alternative text ? Is it limited to only 256 characters or something ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Properties or Tags for Shapes in Excel
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) On Oct 27, 2:39 pm, "Chip Pearson" wrote: 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 Consultingwww.cpearson.com (email on the web site) "MutatedBrain" wrote in message ps.com... Thank you Mr. Pearson. Is there any limitations on alternative text ? Is it limited to only 256 characters or something ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find the custom xml tags in a workbook | Excel Worksheet Functions | |||
How can I use custom properties in an Excel header or formula? | Excel Discussion (Misc queries) | |||
How can I using Excel custom document properties in the header? | Excel Worksheet Functions | |||
How can I set Custom DOC Properties in Word from Excel | Excel Programming | |||
setting properties for shapes | Excel Programming |