Visio Shape Data

[philipbrown77]

Hi,

I am fairly new to Visio shape data and its applications. However I have used Visio for many years. I am trying to create two things:

1: a shape data field that is like a text box - in that you can enter data in multiple lines; this is to store notes/description.
2: a shape data field that looks up to an external source. I have used the fixed value list type that lets you add multiple values, separated by a semicolon, however this doesn't allow for changes to the options to be made very easily. Therefore I would like to link to a list or database that can be easily updated externally which Visio references. Is this possible?


Thanks

[Aeon]

as far as i'm aware it's not possible to have a multiline shape data field.
You can however link data from an external source which has line breaks in it as the source of the data field content.
That is what i'm currently doing: got multiline excel cells which serve as the source of my callouts.

either way in the shape sheet, even the multiline text is represented on 1 single line, and i'm guessing it's the same thing for the regular shape data window.

for the second part of your question i have a suggestion but i haven't quite managed to test it out yet: i'll use excell again as an example, you can always extrapolate it to further your needs

my suggestion is to stick to the semicolonned way of working only make the shapesheet go find the semicolon "string" elsewhere.

take values in excel cells A2-E2 with the values A2:1 B2:2 C2:3 D2:4 E2:5,   

you can then link the datarow from the excel file to the shape where you want to use the dropdownlist,
this will cause the property fields to be created with as titles the name of the title row (if you checked that during the binding of the workbook to the visio drawing)

so say A1-E1 where holding the values option 1 --> option 5
then you can put this in your property you want to show a list for: type =1 / formula ="""&Prop._VisDM_Option1&";"&Prop._VisDM_Option2&";"&......&Prop._VisDM_Option5&"""

(this might have syntax errors in it, as i don't have visio available now and i'm writing this from memory)

once you have done that the value in your dropdownlist will be changed if you change one of the fields in excel. ofcourse if you start adding options you'll have more work.

another option is to do the merger in excel, where you can combine it with a formula like isblank() and if() to check how many values are there and generate the semicolon string dynamically

an example for 20 values that i did try out : rows 1-8 in excel contain A-H

formula used to bring them to semicolon string:

=""&IF(ISBLANK(A1);;A1&";")&IF(ISBLANK(A2);;A2&";")&IF(ISBLANK(A3);;A3&";")&  ...  &IF(ISBLANK(A19);;A19&";")&IF(ISBLANK(A20);;A20)&""
this will return a value like this:

A;B;C;D;E;F;G;H;  (cell C2)

only 8 values although there are 20 fields analyzed
now to remove the last semicolon

=left(C2;len(C2)-1)
which then returns
A;B;C;D;E;F;G;H

then bind that row to your shape, and set the formula for the list to ="""&(Prop._VisDM_nameofcolumn)&"""


hope it helped

[Aeon]

or an easier way to create the semicolon string:

in the cells put
A
;
B
;
C
;
...
etc

then simply use the string Concatenate(h1;h2;h3;...;h..)

i'm sure there's an easy way to do it in visio aswell but that's probably with vba programming and that's not my thing.

[Yacine]

We had this topic this week already:
http://visguy.com/vgforum/index.php?topic=1536.msg6523;topicseen#msg6523

[philipbrown77]

Thank you all for your posts.

I have solved both points now, by using VBA.

1. I have created a custom form that can be opened from the right click menu. On this I was able to add a multiline text box, I then save the data back to the shape on save.

2. Again I have used a custom form this time with a connection to a MS Access database. The dropdown list on the form returns the values from the database and saves them back to the shape data.

Should anyone wish to know more let me know and I'll post some code/example.

Thanks again for the helpful posts.

[Jumpy]

I want more 

I'll have to do sth. similar in the future, so I'm realy interested in your solution.

Thank you,

Jumpy

[philipbrown77]

Note: I have only been using Visio VBA and the SDK for about 3 days, (google is my friend) so this might not be the best way to do this but it worked for me.


Create a new VBA Module with the following code:

Code Select Expand

Public Sub CallComments()
   frmComments.Show
End Sub
This is used to Call the BV windows from the right click menu on the Visio Shape. Add the following to the actions menu

Code Select Expand

=RUNADDON("CallComments")


Create a new user form with a text box, combo box, save and close button. Use the following to populate the fields with the existing data from the shape.

Code Select Expand

Private Sub UserForm_Initialize()
'Dim the data connection
   Dim cn As New ADODB.Connection
'Dim the recordsets for the combo boxes
   Dim rsDropDown As New ADODB.Recordset

'Dim the document attributes
   Dim shpSubjectShape As Visio.Shape

' Gets the name of the current shape
   Set shpSubjectShape = Visio.ActiveWindow.Selection.Item(1)
   
' Give the window a title
   Me.Caption = "Info"
   
'Populate  the text box with the existing data (Text Fields)
   Me.TextBox.Text = shpSubjectShape.CellsU("Prop.ShapeDataValue").ResultStr(visNone)

   
'Create the connection to the access file
   cn.ConnectionString = _
   "Provider=Microsoft.Jet.oledb.4.0;" & _
   "Data Source=C:\Databases\DataBase.mdb;"

'Open the connection
   cn.Open
   
'Get the ComboBox recordset (ComboBox)
   rsDropDown.Open "SELECT DISTINCT [ColumnName] FROM Table1 ORDER BY [ColumnName];", _
            cn, adOpenStatic
   rsDropDown.MoveFirst
   With Me.ComboBox1
       .Clear
       Do
           .AddItem rsDropDown![Role]
           rsDropDown.MoveNext
       Loop Until rsDropDown.EOF
   End With
'Populate the value that is already on the shape for the ComboBox
   Me.ComboBox1.Text = shpSubjectShape.CellsU("Prop.ShapeData1").ResultStr(visNone)

End Sub

And the code for the buttons:

Code Select Expand

Private Sub btnCancel_Click()
'Closes the comments box
   Unload frmComments
End Sub

Private Sub btnSave_Click()
   Dim shpSubjectShape As Visio.Shape
' Gets the name of the current shape
   Set shpSubjectShape = Visio.ActiveWindow.Selection.Item(1)

'Updates the shapes custom data value with the new text box data
   shpSubjectShape.CellsU("Prop.ShapeDataValue").Formula = """" & Me.TextBox1.Text & """"

'Updates the shape with the new combo box data
shpSubjectShape.CellsU("Prop.ShapeDataValue1").Formula = """" & Me.ComboBox1.Text & """"

'Closes the comments box
   Unload frmComments

End Sub

Note this does use two shape data fields: ShapeData and ShapeData1. (renameed in the Shape sheet)

Hopefully the code will help. If you have and questions i'll try and asnwer them as best I can.

I found most of the information I required here: http://www.milestoneconsultinggroup.com/visibility.biz/VTSBlog/Lists/Posts/Post.aspx?List=3e64cee4%2D572c%2D4e23%2Dbef2%2D1a5ac09f3084&ID=13&Source=http%3A%2F%2Fwww%2Emilestoneconsultinggroup%2Ecom%2Fvisibility%2Ebiz%2FVTSBlog%2FLists%2FPosts%2FAllPosts%2Easpx

[Jumpy]

Thank you. That saves me hours of searching the net for informations about this. It's a good point for me to start.

Jumpy

[nmolinos]

I hate replying to such an old post, but I wanted to make sure that people realize that line breaks CAN be inserted into these Shape Data fields without all the workarounds listed above.  They just don't display multiple lines with the exception of the ToolTip text, when hovered.  To insert the new lines, just press CTRL + Enter for a new line.

This is the same method that Microsoft used for entering line breaks into SSIS Package annotations.  Not sure why they thought this was the best way to accomplish this or why they moved away from the Excel ALT + Enter for entering line breaks into cells.