Shapes, Strings, and Userforms

[AlexHP]

OK, so I have officially joined the ranks of those experienced programmers who have absolutely no idea what they are doing trying to program in Visio. While I can easily and successfully program using VBA in Excel I have been doing nothing but bashing my head against the wall trying to work in Visio.

Having said that, here is my issue... I am using Visio 2010 and I have a document which is a multi-page wiring diagram and at the bottom of each page is the page title block, which is a shape with text in it.  I have successfully automated part of it, such as the page numbers, using fields. What I am having problems with is using fields which reference the shape's user-defined cells in order to insert other values.  These values will be entered into a userform and should then be inserted into the appropriate spots in the title block of each page.  For example, the drawing number will be in the format of "0962I-####" where "####" is entered into a textbox in the userform.  Also, there is the "Drawn By:" and "Checked By:" field which will have a two-letter initial in each spot. The problem I am having is that when I process the data from the userform and try to insert it into the shapesheet, it either converts the string into a number or ignores it completely. Here is an example of the code I am using:

Code Select Expand

Private Sub OKButton_Click()
    ' The "OK" button
    '
    Dim vsoDoc As Visio.Document
    Dim vsoPage As Visio.Page
    Dim vsoShape As Visio.Shape
    Dim vsoCell As Visio.Cell
    Dim vsoSect As Visio.Section
    Dim vsoRow As Visio.Row
   
    On Error Resume Next
    RevForm.Hide
    Visio.Application.ScreenUpdating = False
   
    '
    ' *** Drawing Number and Revision ***
    '
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.DrawingNumber")
    vsoCell.Formula = RevForm.DwgNum.Value
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.Revision")
    vsoCell.Formula = RevForm.Rev.Value
    '
    ' *** Unit Type ***
    '
    Select Case UnitType.ListIndex
        Case 0, 1
            UType = "DOAS"
        Case 2
            UType = "DOAS w/ 2-Pos. Damper, MAT"
        Case 3
            UType = "DOAS w/ Mod. Damper, MAT"
        Case 4, 5
            UType = "DOAS w/ Recirc NSB"
        Case 6, 7, 8, 9
            UType = "DOAS w/ Econo"
        Case 10, 11
            UType = "Recirc"
    End Select
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.UnitType")
    vsoCell.Formula = UType
    '
    ' *** Unit Style ***
    '
    Select Case UnitStyle.ListIndex
        Case 0      ' ASC
            UStyle = "ASC"
        Case 1      ' ASHP
            UStyle = "ASHP"
        Case 2      ' WSC
            UStyle = "WSC"
        Case 3      ' WSHP
            UStyle = "WSHP"
        Case 4      ' AHU
            UStyle = "AHU"
        Case 5      ' WTW
            UStyle = "WTW"
    End Select
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.UnitStyle")
    vsoCell.Formula = UStyle
    '
    ' *** Drawing Date ***
    '
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.DrawingDate")
    vsoCell.Formula = RevForm.DwgDate.Value
    '
    ' *** Drawn By ***
    '
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.DrawnBy")
    vsoCell.Formula = RevForm.DwnBy.Value
    '
    ' *** Checked By ***
    '
    Set vsoCell = ActivePage.Shapes("Sheet Info").Cells("User.CheckedBy")
    vsoCell.Formula = RevForm.ChkBy.Value
    Visio.Application.ScreenUpdating = True
    End
End Sub

Any help would be much appreciated. Thank you.

Alex...

[Yacine]

Hi Alex,
From what I read, you're setting and displaying the page data on the pages themselves. That is not the best way to go.
Use instead one common background page for all the foreground pages.
Despite the functionality is limited, there are some built-in functions to get some information from the page displayed automatically.
We discussed this problem a while ago in this topic: http://visguy.com/vgforum/index.php?topic=5911.0

As for the information to display, the simplest way would either be to enter them in custom properties (of a shape on the background page, or the background page itself), or use a dialog. I personally tend to avoid code, when I can use Visio's built-in functionality. And shape data are an obvious way to manipulate data in Visio.
If you still want to add some guidance for the user, a simple shape outside the limits of the drawing can display instruction text.

HTH,
Yacine

[AlexHP]

Thanks, Yacine. While I didn't quite go the direction you suggested, you did get me thinking about another way to make this work. I would upload a sample of what I did but I am having problems getting the file size down to 500KB. I only have 3 pages empty pages, one background page with a few shapes on it, one command button, and some programming code yet I cannot get the file size down to anything less than 2.4MB. If you have any thoughts on shrinking this thing I'm open to suggestions. Thank you.

Alex...

[Yacine]

Yes, empty the document stencil (Stencils / document stencil)

[AlexHP]

I went ahead and created a new document and copied everything over to it. Got it down to about 72kB. Can you give me the procedure to clear the stencil out? I could not get it to work. Here is the document with my solution to the question I previously posed. Let me know what you think. Thank you.

Alex...

[Yacine]

1) under "shapes / more shapes / document stencil " you get shown all the masters ever used in the document. This collection can be very big. Click in the stencil, select all with "ctrl+a", hit DEL.

2) had a look at your drawing. Very nice.
Some thoughts however:
- storing data in several individual shapes and addressing them by their names (eg Set vsoCharacters = vsoPage.Shapes("WorkOrderNum").Characters) looks risky. A bit safer would be to use the shape data of the page (eg vsoPage.Shapesheet.cells("prop.WorkOrderNum")
The shapes displaying the data would reference the page data (thepage!prop.WordOrderNum)
This is heavier, but in my opinion better to maintain.

- using ".characters" to get the text is not clean code, because characters is an object. ".Text" should be safer.
- the LineNumbers shape is smart, but requires code to generate. Alternatively, you can setup one single shape with one number. The number would position the shape automatically
(guard(piny = prop.factor * prop.number + prop.delta)).
In the ondrop event of the shapesheet a formula would increment the counter for each new instance. (setf(getref(prop.number), prop.number+1)
As display, you'd stick with your formula (prop.display = pagenumber()*prop.maxLines-prop.number)
Here also same remark as above - heavier but easier to maintain.

But otherwise great job, thanks for sharing.

[AlexHP]

Thanks, Yacine. I took your advice about the individual shapes to store information and used the Shape data section of the DocumentSheet to store this data.  Works fine so far.

With regards to the line number shape, can you give me some more detail on this? I'm not sure that I understand what you are getting at. Will this still reside on the background page? I would prefer that since it will keep people from deleting or modifying it.  If you could provide a sample I could look at that would be great. Thanks!

Alex...

[Yacine]

Hi Alex,
I enclose a sample in which a shape displays the height formula of your initial shape.
The advantage, is that you have a very simple shape, that just needs to be duplicated.
A further improvement (smartness) could use the pinY of the shape (not implemented).

Best regards,
Y.

[AlexHP]

This is interesting but doesn't quite do what I need it to. The original shape and formula I had would change the numbers if the page was hidden. In other words, if Page-2 was hidden then the numbers on Page-3 would start with "41" (the number next after the last number on Page-1).  The one you made doesn't take that into affect.  Do you think you can make that happen with the shape you made? Please try.  Thank you.

Alex...

EDIT:     Whoopsie!! I just realized that the one I sent you did NOT adjust based upon the visibility of the pages. But I have modified my original to do this since I first sent a copy of my shape in a previous post. So, with that in mind, the question still stands. Do you think you can make your shape modify its numbers based upon page visibility? Let me know...

Alex...

[Yacine]

Now it's time for me to learn something. How do you hide a page?

[AlexHP]

Go to the Page ShapeSheet, then under Page Properties change the UIVisibility setting from "0" to "1".  Be careful when you do this because I think it also removes the page from the Drawing Explorer, in which case you cannot change it back without using the undo button (or formulas, or VBA).  What I do is add a user row to the Document ShapeSheet and call it "User.PagesVisibility".  In that cell I add the array ="0;0;0;0;0" (the quantity of "0" equals the number of pages with the leading "0" corresponding to all background pages). Then on each page I add the formula "=INDEX(PAGENUMBER(),TheDoc!User.PagesVisibility,,0)" in the UIVisibility cell. This will refer to the array you added in the Document Shapesheet. When you want to hide a page, say Page-2, change the array in the Document Shapesheet to ="0;0;1;0;0" and the page will hide. Then when you want it back just change the array again. There is a good blog post about hiding pages at the link below as well as a sample document you can download. Then follow the link at the end of that post to another post about hiding pages and another sample document. Enjoy!

http://visualsignals.typepad.co.uk/vislog/2007/11/hiding-pages.html

Alex...

[Hey Ken]

Now it's time for me to learn something. How do you hide a page?

Yacine:

   The impetus is great to say...

My son does also always wait that the objects find him.

...but rather let me say...

The page's shapesheet has a cell named UIvisibility.  Set it to 0, and the page is visible.  Set it to 1 and the page is invisible.

;-)

- Ken

[Yacine]

I did not deserve this taunt.
Just got tired of answering the very same questions over and over and seeing that the person did obviously not do any research.
The posts of this exact similar question were still in the main listing of the forum.

But your post is funny and we are here for fun.
Cheers,
Y.