Section Four : ShapeSheets Conversing with External Data

 

ShapeSheet - Chapter 42: Data Repositories in SmartShape Symbols and ShapeSheets

As we know, Visio SmartShape symbols are more than just graphical images on a page which represent data and processes. They are, in fact, repositories of, potentially, large quantities of data. Just as you would think of a ShapeSheet as being an Excel Spreadsheet behind every SmartShape symbol, Shape Data can be thought of as an Access Database Table behind every SmartShape Symbol. In fact, all of the diverse data repositories behind each SmartShape symbol are record sets in the Diagram’s Database.

Golden Oldies – Data1, Data2, and Data 3 Fields

From the earliest days of Visio there have always existed three data repositories within a SmartShape symbol which have been designed to hold absolutely any form of data; text, numbers, even binary streams. These Data Repositories are the Data 1, Data 2, and Data 3 fields. Be aware that these fields do NOT have an interface in a ShapeSheet. They ARE addressable as Text Fields as well as via automation calls.

Because these data repositories still exist, and because you can address them through ShapeSheet methodologies, I am including a “how – to”, if you will, to create a “note taking” application from within a Visio SmartShape symbol which allows you access to these fields in a FAR more robust manner than the default. From the default, you say? Just what IS this default? Below I have included a screen shot of the default interface to these three fields. They are, by default, accessed via the Format > Special selection with a Right-Click on a SmartShape symbol, accessing its Context Menu.

As you can see, the interface to enter data into, and subsequently, to read data from, the Data 1, Data 2, and Data 3 fields in simply a one line text field in the Dialog form. This is absolutely less than optimal.

I am going to walk you through an exercise in creating a far more robust interface to these data repositories. You can include access to this interface for ANY SmartShape symbol by simply adding an Actions row to your ShapeSheet for your SmartShape symbol.

Create a new Comments Dialogue for Microsoft Office Visio 2007 SmartShape Symbols.

 

Within Microsoft Office Visio 2007, there exist two distinct methods of adding comments “associated” with any given Visio SmartShape symbol. The first of these is the Microsoft Office Comments Object. This is added by selecting a SmartShape Symbol and then selecting Insert > Comment from Visio’s Insert Menu. This is somewhat less than effective since if the symbol is moved, the comment does not move along with the Symbol and hence becomes quite orphaned. The Second is to ensure that the subject SmartShape Symbol is selected and then select Insert > Insert Shape Screen Tip… from the Visio Insert Menu. After adding the comment information in the dialog, and the subsequent closing of the dialog, whenever you hover the mouse cursor over the SmartShape Symbol, the Screen Tip (Comment) pops up for a brief time as a Pop-Up Screen Tip. The advantage of this is that the Screen Tip always remains associated with the SmartShape symbol. However, the down-side is that is only temporarily visible, and it can only display a limited number of characters. Additionally, there is no method for accessing these Screen Tips with Visio Custom Reports.

I am going to show you how to implement a Powerful Comments Dialog in Visio which may be associated with ANY Visio SmartShape symbol, and can hold up to as much as 64K of text. Additionally the comments can remain on screen until the Comments Dialog is Closed. Again… This technique utilizes a field contained in every SmartShape symbol, which has been around since Visio 1.0. Every SmartShape Symbol contains three data fields accessible by end users: the Data1, Data2, and Data3 fields. To access these, Right-Click on any Visio SmartShape Symbol and select Format > Special from the SmartShape’s Context Menu. In the Format Special Dialog you will see three single-line data entry fields: Data1, Data2, and Data3. Each holds up to 64K of ANY sort of data. With the introduction of Microsoft VBA and VBA Forms with Visio 3.0, Visio has all of the tools necessary to implement an elegant User Interface to add, edit, and delete text into a data field through a VBA form.

I will detail exactly how to implement this feature. With a bit of practice you will be able to greatly enhance and embellish on this technique to create a robust comments repository associated with any SmartShape symbol.

The Steps necessary to create this interface are relatively simple:

1.       Create the User Form

2.       Add the VBA ActiveX Controls to the Form

3.       Create the Code to initialize the Form

4.       Create the Code to Save the Comment changes when the Form is Closed

5.       Create the Public Subroutine to call and initialize the Form

6.       Create the Context Menu and associated Action to Call the Public Subroutine

We will cover each of these tasks in a step-by-step, click-by-click manner.

The appendix at the end of this section of this text lists the entire code implementation for this exercise.

Create a new Microsoft Office Visio 2007 Diagram

In Visio 2007 Select File > New > General > Basic Diagram (US units) from the File Menu

Enter the Microsoft Office 2007 Visual Basic for Applications Development Environment

In Visio 2007 Select Alt+F11

Ensure that the VBA Project Explorer is Visible

In the VBA IDE Select View > Project Explorer from the VBA View Menu

Select the current Diagram Project

In the VBA IDE, in the Project Explorer, Click on the open Diagram name (i.e. Drawing 1 (Drawing1))

Create a new VBA Form

In the VBA IDE Select Insert > User Form from the VBA Insert Menu
(in the VBA Code Area a new window will be visible, initially entitled “Drawing1 – UserForm1 (UserForm)”
Within the Form Design Window a new blank form will be visible, initially entitled “UserForm1”

Ensure that the VBA Properties Window is Visible

In the VBA IDE Select View > Properties Window from the VBA View Menu

Ensure that the VBA Toolbox is Visible

In the VBA IDE Select View > Toolbox from the VBA View Window

Edit the Properties of the newly created User Form

In the VBA Properties Window modify the values for the following Property Fields (note that the field name is to the left of the table in the Properties Window, the field value is to the right of the table):

(name) = “frmCommentsDialogue”
(Note that this will change the title of the Properties Window, as well as the value in the drop-down-list immediately below the Properties Window Title, as well as the Header in the Form Design Window)

Caption = “Comments Dialogue”
(Note that this will change the Title in the User Form to “Comments Dialogue”

Add a Text Box Control to the Comments Dialog

In the VBA IDE, Click anywhere within the open area of the Form Design Window to display the toolbox and prepare the new User Form to accept the Text Box ActiveX Control

In the VBA IDE, In the Toolbox Click to select the Text Box ActiveX Control Item (it looks like a small “ab|”

Move your cursor to the User Form.

Click – Drag – and release to drag out a Text Box as shown in Figure 1

Figure 1

Edit the Properties of the newly created Text Box

In the VBA Properties Window modify the values for the following Property Fields:

(name) = “txCommentsSurface”

EnterKeyBehavior = “True”

MultiLine = “True”

ScrollBars = “3 – fmScrollBarsBoth”

WordWrap = “True”

Add a Command Button Control to the Comments Dialog

In the VBA IDE, Click anywhere within the open area of the Form Design Window to display the toolbox and prepare the new User Form to accept the Command Button ActiveX Control

In the VBA IDE, In the Toolbox Click to select the Command Button ActiveX Control Item (it looks like a small raised rectangular box i.e. button)

Move your cursor to the User Form.

Click – Drag – and release to drag out a Command Button as shown in Figure 2

Figure 2

Edit the Properties of the newly created Command Button

In the VBA Properties Window modify the values for the following Property Fields:

(name) = “btnClose”

Caption = “Close”

Initialize the configuration of the Comments Dialog when the Dialog Loads

In the VBA IDE, In the Form Design Window, in the Comments Dialog, Double-Click on any open area of the Dialog Form (i.e. any area with the grid of dots pattern)
Note a new Code Window will Open

Create the Code Stub for the Dialog Initialization Event

In the VBA IDE, In the New Code Window, ensure that the Left Drop-Down List near the top of the Code Window has shown “UserForm”.

In the VBA IDE, In the New Code Window, ensure that the Right Drop-Down List near the top of the Code Window as shown “Initialize”

In the VBA IDE, In the New Code Window, ensure that the mouse cursor is blinking at the beginning of the blank line between the line reading “Private Sub UserForm_Initialize()”, and “End Sub”

Create the VBA Code for the Form Initialize Event

In the VBA IDE, in the New Code Window, beginning at the point where the blinking Mouse Cursor exists, enter the following Visual Basic For Applications (VBA) Code:

           Dim shpSubjectShape As Visio.Shape

    Set shpSubjectShape = Visio.ActiveWindow.Selection.Item(1)

    Me.Caption = "Comments Dialog : " & shpSubjectShape.Name

    Me.txCommentsSurface.Text = shpSubjectShape.Data1

    Me.txCommentsSurface.SelStart = 0

The DIM statement means to Dimension or Declare a VBA Variable to hold an Object. In this case you declare a variable named shpSubjectShape and declare it as an object data type of Visio Shape

When you interact with an Object to Get or Set Properties, Methods, or Events you use the VBA Keyword SET. A quick way to get a reference to the selected SmartShape symbol is to go to the Visio Application Object and look at the Active Window Object and look at the Selection Object and get a reference to the first thing selected; in this case the singular SmartShape Symbol you have selected.

The VBA Keyword ME is a VBA shortcut to the VBA Form currently in use. Therefore the Caption property of the Form is being set with the string “Comments Dialog” and the actual name of the selected shape.

The next line binds whatever text is in the Data1 field of the Shape to the Text Property of the Text Box on the Current Form

Finally the last line simply locates the curser at the beginning of the body of text in the Text Box.

Create the Code Stub for the Dialog Close Event assigned to the Command Button

In the VBA IDE, Click on the Header for the Comments Dialogue User Form

In the VBA IDE, In the Form Design Window, in the Comments Dialog, Double-Click on the Command Button on the Comments Dialog Form
Note the Code Window will rise to the top of the Code Windows Stack

In the VBA IDE, In the New Code Window, ensure that the mouse cursor is blinking at the beginning of the blank line between the line reading “Private Sub btnClose_Click()”, and “End Sub”

Create the VBA Code for the Form Close Event

In the VBA IDE, in the New Code Window, beginning at the point where the blinking Mouse Cursor exists, enter the following Visual Basic For Applications (VBA) Code:

    Dim shpSubjectShape As Visio.Shape

    Set shpSubjectShape = Visio.ActiveWindow.Selection.Item(1)

    shpSubjectShape.Data1 = Me.txCommentsSurface.Text

    Unload frmCommentsDialogue

This subroutine is very similar to the module described above. However as you can see in the last two lines, you simply reverse the binding and push the text from the Text Box control back to the Data1 field of the Shape. You then close and unload the User Form.

Create a new Public VBA Subroutine to Call for the Initialization and Loading of the Comments Dialogue Form

In the VBA IDE Select Insert > User Module from the VBA Insert Menu
Note that the Code Window’s Header will have a title reading “Drawing1 – Module1 (Code)”

In the VBA IDE, In the Module1 Code Window, enter the following VBA Code:

Public Sub CallCommentsDialogue()

    frmCommentsDialogue.Show

End Sub

This tiny module simply exists as a Public Subroutine which calls for the initialization and showing of the VBA Form.

This completes the VBA Form and VBA Code Creation for the Comment s Dialogue. You may now close the VBA IDE

From the VBA IDE, Select File > Close and Return to Visio from the VBA File Menu.

Assign the Code to call the Comments Dialog to any desired Visio SmartShape Symbol.

In the Visio User Interface, drag any desired Visio Master from a Visio Stencil onto the Visio Diagramming Surface.

Open the ShapeSheet for the SmartShape Symbol

Ensure that the desired Visio SmartShape Symbol is currently the only selected SmartShape Symbol on the current Visio Page.

From the Visio User Interface, Select Window > Show ShapeSheet from the Visio Window Menu.

Add the Function Call to trigger the Comments Dialog

With the Visio ShapeSheet Window Open and active, Select Insert > Section… From the Visio Insert Menu.

In the Insert Section Dialog, check the Check Box Control labeled “Actions”, then Click the OK Command Button to close the Insert Section Dialog.

Scroll down through the various ShapeSheet Sections until you find the Actions Section of the ShapeSheet. Notice that there are numerous cells in the available row. The first cell at the far left of the row will have the Column Header labeled “Actions” and will have a value reading “Actions.Row_1”. Click into the cell and type the following text “CallCommentsDialogue”. Press the Enter key to accept this edit.  The cell will now read as “Actions.CallCommentsDialogue”.

The next cell to the Right has a Column Header entitled “Action”. In the cell immediately below the header , enter the following formula “=RUNADDON( “CallCommentsDialogue”)”. Press the Enter to accept this edit.

RUNADDON() is a ShapeSheet Function which calls for and runs a VBA Subroutine or Function. Note that the name of the Subroutine or Function must be entered as a Quoted String. In this case RUNADDON is calling the Public Subroutine “CallCommentsDialogue”.

The next cell to the Right will have a Column Header reading “Menu”. In the cell immediately below the header , enter the following formula “Add or Edit Comments for this SmartShape Symbol”. Press the Enter to accept this edit. The Quoted Text is the text that will physically appear on the Context (Right-Click) Menu.

Close the ShapeSheet Environment

Test the Comments Dialogue

Right-Click on the Selected SmartShape Symbol and select “Add or Edit Comments for this SmartShape Symbol” from the SmartShape Symbol’s Context (Right-Click) Menu.

When the Dialogue Opens, begin typing comments into the Dialog in the Multi-Line Text Box. When your additions and edits are complete, Click the “Close” Command Button to accept the additions and edits and close the Dialog.

Note that the Dialog displays the header inclusive of the selected SmartShape Symbol’s Name to aid in easy identification of the SmartShape to which the comments are being attached.

Each SmartShape Symbol is capable of holding up to 64K of text.

Listing 1 – VBA Code

Module 1

Public Sub CallCommentsDialogue()

    frmCommentsDialogue.Show

End Sub

User Form 1

Private Sub btnClose_Click()

    Dim shpSubjectShape As Visio.Shape

    Set shpSubjectShape = Visio.ActiveWindow.Selection.Item(1)

    shpSubjectShape.Data1 = Me.txCommentsSurface.Text

    Unload frmCommentsDialogue

End Sub

 

Private Sub UserForm_Initialize()

    Dim shpSubjectShape As Visio.Shape

    Set shpSubjectShape = Visio.ActiveWindow.Selection.Item(1)

    Me.Caption = "Comments Dialog : " & shpSubjectShape.Name

    Me.txCommentsSurface.Text = shpSubjectShape.Data1

    Me.txCommentsSurface.SelStart = 0

End Sub

FORM Code

VERSION 5.00

Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} frmCommentsDialogue

   Caption         =   "Comments Dialogue"

   ClientHeight    =   3225

   ClientLeft      =   45

   ClientTop       =   330

   ClientWidth     =   4710

   OleObjectBlob   =   "frmCommentsDialogue.frx":0000

   StartUpPosition =   1  'CenterOwner

End

Attribute VB_Name = "frmCommentsDialogue"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = True

Attribute VB_Exposed = False

I realize that this exercise strayed a bit from pure ShapeSheet development, however, I feel that the effort was neither too daunting nor too divergent from adding a formidable new tool to your toolkit in enhancing the power of SmartShape symbols in your applications.

Shape Data – Used to be Custom Properties

As stated before, Data 1, Data 2, and Data 3 have been available as Data Storage Repositories since the early days of Visio. It became obvious that these three storage areas were not enough to hold the important data which needed to be associates with SmartShape symbols, and so Custom Properties were created. Custom Properties go a long way back in the history of Visio as well. You can think of Custom Properties as being simple Access Databases associated with SmartShape symbols and their ShapeSheets. Unlike Data 1 through Data 3, these Custom Properties are directly accessible via the ShapeSheet.

Beginning with Visio 2007, these Custom Properties have been renamed. They are now called Shape Data. Internal to the ShapeSheet they still, for backward compatibility reasons, carry the Cell designator of “Prop.[row name]/[row index]”

By default a simple SmartShape symbol carries no Shape Data. To add Shape Data to a ShapeSheet (and hence, the SmartShape symbol itself) select Insert > Section from the Visio Insert menu in the ShapeSheet Context, then add additional Rows as necessary by Clicking into an existing Shape Data Row and selecting Insert > Row from the Visio Insert menu in the ShapeSheet Context.

Shape Data can also be added through the user interface by Right-Clicking on a SmartShape symbol and selecting Data > Shape Data… from the SmartShape symbol’s Context menu, then clicking the Define… Command Button. When you have completed this action, the following Dialog will be opened:

There are 11 Fields in the dialog. These directly correspond to the Cells in a Shape Data Row in a ShapeSheet. The ShapeSheet row is shown directly below:

Using the Dialog as a basis, here is the mapping between the Dialog and the ShapeSheet

Label = Label
Name = Prop.[user provided name] in the leftmost Cell of the Row
Type = Type
Language = LangID
Format = Format
Calendar = Calendar
Value = Value
Prompt = Prompt
Sort key = SortKey
Ask on drop = Ask
Hidden = Invisible

The Shape Data Dialog, as seen by the user is shown below:

Label is the Text String which is shown as a label to the Left of the Text Box where the Value is entered.

Name is the actual Shape Data Row name in the ShapeSheet. NOTE: Whereas the Label is a simple Text String and can contain spaces, the Name must comply with ShapeSheet standards for Row naming, i.e. no spaces, letters, numbers, underscore characters, and no special characters.

Type is the Data Type as selected in the Shape Data Definition Dialog. These can be String, Number, Fixed List, Variable List, Boolean, Currency, Date, or Duration. When entered directly in a ShapeSheet Shape Data Row, the Data Type Choice must be entered as an integer where String = 0, Fixed List = 1, Number = 2, Boolean = 3, Variable List = 4, Date = 5, Duration = 6, and Currency = 7

Language ID is the Language Definition for the target use. 1033 is the code for US English

Format has multiple use. When applied to String, Number, Date, Duration, or Currency, it is the Formatting Picture String. When applied to Fixed List or Variable List it is a semi-colon-deliminated list as a text string which lists the possible entries for the user to select from, as in “able;baker;charlie;delta;easy”.

Calendar is the Calendar Type Definition as defined by Visio. 0 is the Western Calendar. There are 9 possible calendar definitions available within Visio.

Value is the actual repository where data input into a Shape Data dialog is stored. It is the default cell for a Shape Data Row.

Prompt is the Text String as a prompt to the user as shown in the lower area of the Shape Data Dialog. Please note that it is considered poor SmartShape symbol programming practice to state the obvious as a prompt. In other words, if you are asking for a text based entry which will hold data about an Employee, the prompt should be something like “Enter the Employee’s Name here” rather than “Type text here”. If the Data Type is a Fixed List (Drop Down List), the prompt would begin “Select… from the list of available …”. If the Data Type is a Variable List (Combo Box), the prompt would begin “Select, or Enter…”

Sort Key is the order in which you want the (presuming that you have more than one) Shape Data Rows to be displayed in the Dialog. Note that these ARE NOT in alphabetical or numeric order. They are in ASCII Character order. In other words, if you use a sort order sequence of 1,2,3,4,5,6,7,8,9,10,11, they will show in the dialog in the order of 1,10,11,2,3,4,5,6,7,8, and 9. To have them ordered properly, use the sequence 01,02,03,04,05,06,07,08,09,10, and 11.

Ask on Drop determines whether the particular Shape Data item will be displayed in the Shape Data dialog when the SmartShape symbol is dropped and instanced from a Master on a Stencil, or when the SmartShape symbol is copied. If True it will be initially displayed (in the older style Shape Data dialog), if False it will not be displayed. Any subsequent display of the Shape Data dialog will show ALL entries (based upon their Hidden [Invisible] Property).

Hidden is whether or not you wish the user to see this Shape Data item in the Shape Data Dialog or not. A value of True hides the Row Item, a value of False shows the Row Item.

There is no practical limitation to the number of Shape Data items which can be associated with a SmartShape symbol. However, note that there are two differing styles of the Shape Data Dialog. The default Shape Data dialog (selected by Right-Clicking on a SmartShape symbol and selecting Data > Shape Data… from the SmartShape symbol’s Context menu) is screen resolution dependent. It can display between 19 and 24 items. If there are more defined Shape Data items, they will not display in the dialog, and a warning will appear in the dialog informing the user that “additional items are not shown…”. The new Shape Data Window (selected by selecting View > Shape Data Window from the Visio View menu) is a scrollable dialog which will show ALL defined Shape Data items.

Why is it valuable to add Data to the SmartShape symbol? Because data rich SmartShapes can be included in Custom Reports and other output mechanisms. Data Rich SmartShape symbols are able to display their data when accessed in an Internet Explorer browser subsequent to a Save As Web Page operation in Visio. Data Rich diagrams are what set Visio apart from other simple graphic tools like PowerPoint. The ability to store, report on,  and display data is the value add that sets  your solution apart from “pretty pictures”.