Using Visio as a Forms Tool: Checkbook Example
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
David A. Edson
December 1999
Summary: Demonstrates the capabilities of Visio 2000 in creating and using forms-based processing. (10 printed pages)
Download Chequebook.exe.
With each release of Visio®, and especially with the release of Microsoft® Visio 2000, an ever-growing number of users are becoming aware of the power of Visio solutions. An increased number of developers are beginning to utilize Visio as their development platform of choice. However, very few users or developers have begun to tap into an aspect of Visio development that differs from the solutions that ship "within the box." Visio is also an excellent forms tool. After ascertaining an organization's needs for forms-based processing, developers can easily deal with highly complex requirements by designing those forms within Visio, employing the power of Visio's Visual Basic® for Applications ("VBA") automation processing.
The example that follows, a simple checkbook and ledger application, is designed to acquaint you with some of Visio's capabilities in the creation and use of forms-based processing. The majority of the functionality is available under both Visio 5.0 and Visio 2000. Where a feature is specific to Visio 2000, I will call this to your attention.
Launch Visio and begin a new diagram based on the "chequebooktemplate.vst" template file. When the diagram is loaded, you will see a single-page document with a check and a ledger displayed on the default page. Immediately beneath the check is the "write Cheque," a Microsoft ActiveX® control that takes the information on the face of the check and enters it into the ledger below.
Before you use this button, take some time to familiarize yourself with the check itself. The check is a collection of Visio SmartShapes® symbols, but the symbols have been modified in their behavior so that the check behaves like a standard form. Note that there are no sizing handles on any of the symbols. None of the symbols can be resized or repositioned, nor can they be rotated nor deleted. A symbol that can and should be selectable contains a simple green-dashed selection indicator line around it. A symbol that should not be selected has no selection user interface associated with it. Move the pointer over any symbol that should be selected. If it rests there for a moment, a tip appears, assisting you in the interaction with this component of the form. This feature is new to Visio 2000, an implementation of a Comments cell in the Visio ShapeSheet® environment. The Miscellaneous section of the ShapeSheet now has a Comments cell. Anything placed in this Comments cell will appear whenever the cursor is floated over the subject SmartShape symbol. The user also has access to this functionality by selecting Insert > Edit Comment… from the Visio menu system.
Begin by moving the pointer over the Payee Information area in the upper right-hand corner of the check. Note that when your pointer rests over this area, the tip then states: "Right-click to edit Payee Information." When you right-click, and select Edit Payee Information from the context menu, the Visio Custom Properties dialog box appears. You are now able to edit any of the information about the person holding the account on which the check is drawn. This click and select behavior is a function of adding an Actions section to the ShapeSheet of the SmartShapes symbol for the Payee information. The formula in the Action cell of the Actions section is:
"=DOCMD(1312)"
The Visio command number for the Open the Custom Properties dialog box is 1312. This easy-to-program feature has been available since Visio version 3.0.
Next move you pointer over the date area of the check. The tip states: "Double-click to change date." When you double-click on the date item, the Visio Custom Properties dialogue box appears again. This SmartShapes symbol has a single Custom property, the date of the check. You can type any date you wish, but Visio 2000 also allows you to select any required date in a new calendar control. The launching of the Visio Custom Properties dialogue box is the same as with the Payee Information symbol, but is triggered with a double-click. This is accomplished by placing the formula into the "EventDblClick" cell of the Events Section of the ShapeSheet for the Date SmartShapes symbol.
To the right of the "Pay To The Order Of" text on the face of the check is a text entry area for the Pay To information. Move the pointer over the Pay To area of the check. Note that when your pointer rests over this area the tip then states: "Click and type to enter To Whom Paid." Clicking and then typing here places the text as Text on the SmartShapes symbol for the Pay To information SmartShapes symbol. The same is true of the For (memo) area in the lower left corner. The tip here states: "Click and type to post Memo for this Cheque." The signature block is in the lower right corner. The tip here states: "Click and type your name to sign this cheque."
The center-right area of the check contains yet another text-based SmartShapes symbol. Here the tip states: "Click and type to enter Amount to be Paid. Do not use commas in this entry." The tip not only tells the user what information is required but the format needed for proper processing.
Once the amount has been entered in the Amount To Be Paid symbol, move your pointer over the text area for the previous numeric entry. This is where the numeric amount is spelled out on the face of the check. Note that the "tip" here states: "Double-click to post amount." As we have seen before, the double-click is enabled via the "EventDblClick" cell of the symbol's ShapeSheet. This time, however, the formula contained in that cell has the following entry:
"=RUNADDON("ParseAmount")"
This is a call to run a Visio add-on, which is a VBA function. The purpose of the function is to take the numeric data shown as text on the face of the Amount To Be Paid symbol and return it as spelled-out text. The code for this ParseAmount subroutine follows:
Public Sub ParseAmount()
Dim shpObjAmountItem As Visio.Shape
Dim shpObjAmountTextItem As Visio.Shape
Dim szAmountInText As String
Dim szAmountInTextTemp As String
Dim szAmountOutText As String
Dim intStrLen As Integer
Set shpObjAmountItem = Visio.ActivePage.Shapes.Item("AmountItem")
Set shpObjAmountTextItem =
Visio.ActivePage.Shapes.Item("AmountTextItem")
szAmountInText = shpObjAmountItem.Text
szAmountInTextTemp = szAmountInText
intStrLen = Len(szAmountInText)
If Mid(szAmountInText, intStrLen - 2, 1) = "." Then
szAmountOutText = ParseTwo(Mid(szAmountInText, intStrLen - 1,_
2))
szAmountOutText = " and " & szAmountOutText & " Hundredths"
szAmountInTextTemp = Left(szAmountInTextTemp, intStrLen - 3)
End If
If Len(szAmountInTextTemp) > 2 Then
szAmountOutText = ParseTwo(Mid(szAmountInTextTemp,
Len(szAmountInTextTemp) - 1, 2)) & _
szAmountOutText
szAmountOutText = ParseTwo(Mid(szAmountInTextTemp,
Len(szAmountInTextTemp) - 2, 1)) & "Hundred" & _
szAmountOutText
szAmountInTextTemp = Left(szAmountInTextTemp,
Len(szAmountInTextTemp) - 3)
ElseIf Len(szAmountInTextTemp) > 0 Then
szAmountOutText = ParseTwo(Mid(szAmountInTextTemp,
Len(szAmountInTextTemp) - 1, 2)) & szAmountOutText
szAmountInTextTemp = ""
End If
If Len(szAmountInTextTemp) > 2 Then
szAmountOutText = ParseTwo(Mid(szAmountInTextTemp,
Len(szAmountInTextTemp) - 1, 2)) & "Thousand" & _
szAmountOutText
szAmountOutText = ParseTwo(Mid(szAmountInTextTemp,
Len(szAmountInTextTemp) - 2, 1)) & "Hundred" & _
szAmountOutText
szAmountInTextTemp = Left(szAmountInTextTemp,
Len(szAmountInTextTemp) - 3)
ElseIf Len(szAmountInTextTemp) > 0 Then
szAmountOutText = ParseTwo(Mid(szAmountInTextTemp,
Len(szAmountInTextTemp) - 1, 2)) & "Thousand" & _
szAmountOutText
szAmountInTextTemp = ""
End If
shpObjAmountTextItem.Text = szAmountOutText
End Sub
Public Function ParseTwo(ByVal szPassedText As String) As String
Dim intPassedText As Integer
Dim szTempStr As String
intPassedText = Val(szPassedText)
Select Case intPassedText
Case 1
szTempStr = "One"
Case 2
szTempStr = "Two"
Case 3
szTempStr = "Three"
Case 4
szTempStr = "Four"
Case 5
szTempStr = "Five"
Case 6
szTempStr = "Six"
Case 7
szTempStr = "Seven"
Case 8
szTempStr = "Eight"
Case 9
szTempStr = "Nine"
Case 10
szTempStr = "Ten"
Case 11
szTempStr = "Eleven"
Case 12
szTempStr = "Twelve"
Case 13
szTempStr = "Thirteen"
Case 14
szTempStr = "Fourteen"
Case 15
szTempStr = "Fifteen"
Case 16
szTempStr = "Sixteen"
Case 17
szTempStr = "Seventeen"
Case 18
szTempStr = "Eighteen"
Case 19
szTempStr = "Nineteen"
Case 20
szTempStr = "Twenty"
Case 30
szTempStr = "Thirty"
Case 40
szTempStr = "Forty"
Case 50
szTempStr = "Fifty"
Case 60
szTempStr = "Sixty"
Case 70
szTempStr = "Seventy"
Case 80
szTempStr = "Eighty"
Case 90
szTempStr = "Ninty"
Case Else
Dim szTempStr1 As String
Dim szTempStr2 As String
szTempStr1 = Left(szPassedText, 1) & "0"
szTempStr2 = Right(szPassedText, 1)
szTempStr = ParseTwoAgain(szTempStr1)
szTempStr = szTempStr & ParseTwoAgain(szTempStr2)
End Select
ParseTwo = szTempStr
End Function
Public Function ParseTwoAgain(ByVal szPassedTextalt As String) As String
Dim intPassedTextalt As Integer
Dim szTempStralt As String
intPassedTextalt = Val(szPassedTextalt)
Select Case intPassedTextalt
Case 1
szTempStralt = "One"
Case 2
szTempStralt = "Two"
Case 3
szTempStralt = "Three"
Case 4
szTempStralt = "Four"
Case 5
szTempStralt = "Five"
Case 6
szTempStralt = "Six"
Case 7
szTempStralt = "Seven"
Case 8
szTempStralt = "Eight"
Case 9
szTempStralt = "Nine"
Case 10
szTempStralt = "Ten"
Case 11
szTempStralt = "Eleven"
Case 12
szTempStralt = "Twelve"
Case 13
szTempStralt = "Thirteen"
Case 14
szTempStralt = "Fourteen"
Case 15
szTempStralt = "Fifteen"
Case 16
szTempStralt = "Sixteen"
Case 17
szTempStralt = "Seventeen"
Case 18
szTempStralt = "Eighteen"
Case 19
szTempStralt = "Nineteen"
Case 20
szTempStralt = "Twenty"
Case 30
szTempStralt = "Thirty"
Case 40
szTempStralt = "Forty"
Case 50
szTempStralt = "Fifty"
Case 60
szTempStralt = "Sixty"
Case 70
szTempStralt = "Seventy"
Case 80
szTempStralt = "Eighty"
Case 90
szTempStralt = "Ninty"
End Select
ParseTwoAgain = szTempStralt
End Function
The ParseAmount subroutine calls the ParseTwo function, which in turn calls the ParseTwoAgain function to fully return the qualified string back to the text of the SmartShapes symbol.
Finally, with all proper fields on the face of the check filled in, the user is ready to "write" the check and add it to the ledger. Note that the check is numbered. When the new document was created (when the document is opened, the diagram is immediately placed in RunMode), the page itself contained a sequence number. This sequence number was entered into the check's upper right-hand corner with the following sequence numbering code.
Private Sub Document_RunModeEntered(ByVal doc As IVDocument)
Dim counter As Integer
counter =
Visio.ActivePage.PageSheet.Cells("Prop.ChequeSequence").ResultInt
(visNumber, 0)
counter = counter + 1
Visio.ActivePage.PageSheet.Cells("Prop.ChequeSequence").Formula =
counter
End Sub
This routine increments the number as a Custom Property for the Page itself. The check always looks to the page to fulfill its information via an intershape reference.
When the user finally clicks the ActiveX control on the page to add the check to the ledger, the click event for the control is triggered. Keep in mind that the ledger is nothing more than an embedded Microsoft Excel spreadsheet. The click event contains the following code, adding to the ledger.
Private Sub CommandButton1_Click()
' Export to Embedded Excel Spreadsheet
Dim xlSheet As Excel.Worksheet
Dim rowNum As Integer
Dim counter As Integer
Set xlSheet = ActivePage.Shapes("ExcelSheet").Object.Worksheets(1)
rowNum =
Visio.ActivePage.PageSheet.Cells("Prop.ChequeSequence").ResultInt
(visNumber, 0) + 2
'A = Date, B = Check #, C = Memo, D = Amount, F = Balance
xlSheet.Range("A" & LTrim(Str(rowNum))).Formula =
Visio.ActivePage.Shapes.Item("DateItem").Cells("Prop.Date").Result
(visDate)
xlSheet.Range("B" & LTrim(Str(rowNum))).Formula = Format(Str
(rowNum - 2), "000#")
xlSheet.Range("C" & LTrim(Str(rowNum))).Formula =
Visio.ActivePage.Shapes.Item("ToWhomItem").Text
xlSheet.Range("D" & LTrim(Str(rowNum))).Formula =
Val(Visio.ActivePage.Shapes.Item("AmountItem").Text)
xlSheet.Range("F" & LTrim(Str(rowNum))).Formula = xlSheet.Range
("F" & LTrim(Str(rowNum - 1))).Value - xlSheet.Range("D" &
LTrim(Str(rowNum))).Value
counter =
Visio.ActivePage.PageSheet.Cells("Prop.ChequeSequence").ResultInt
(visNumber, 0)
counter = counter + 1
Visio.ActivePage.PageSheet.Cells("Prop.ChequeSequence").Formula = _
counter
Visio.ActivePage.Shapes.Item("AmountItem").Text = ""
Visio.ActivePage.Shapes.Item("AmountTextItem").Text = ""
Visio.ActivePage.Shapes.Item("MemoItem").Text = ""
Visio.ActivePage.Shapes.Item("ToWhomItem").Text = ""
End Sub
This code is designed to do nothing more than take the data from the faces of the applicable SmartShapes symbols and hand it off to the appropriate cell within the Microsoft Excel spreadsheet. It then calculates the new balance by subtracting the check amount entry from the previous balance. The subroutine then increments the sequence number for the check and clears out the entries on the face of the check.
To fully view all of the data in the ledger, as the text below the ledger states, double-click the ledger to activate in-place and scroll as necessary.
This application gives you some idea of the power of Visio as a forms-based tool. Keep in mind that users of Visio solutions do not necessarily need to "drag-and-drop" to work within a solution. There are many, many applications where forms-based processing is a critical part of an organization's daily activity. Freight and shipping companies, order-entry groups, patient-care facilities, and numerous others all use forms-based processing in their work. Use Visio in new and creative ways to leverage the power of graphical tools and create your own forms-based applications.