Solved

Macro Help Hiding ShapeData on an Organizational Chart

Posted on 2010-09-15
6
781 Views
Last Modified: 2012-05-10
I've recorded the following Macro to hide sensitive employee data within the ShapeData on a Visio Org. Chart; I would like your assistance in editing the macro to apply to all shapes (employees) across multiple worksheets.

Also if the ordering or number of data fields in shape data were to change would it affect this macro?
Sub HideSensitiveShapeData()

'

' Hides sensitive employee information

'

' Keyboard Shortcut: Ctrl+Shift+H

'



    Dim UndoScopeID1 As Long

    UndoScopeID1 = Application.BeginUndoScope("Shape Data")

    Dim vsoShape1 As Visio.Shape

    Dim intPropRow2 As Integer

    Set vsoShape1 = Application.ActiveWindow.Page.Shapes.ItemFromID(7)

    intPropRow2 = 4

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 7

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 8

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 9

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 10

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 13

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 14

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 15

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    intPropRow2 = 16

    vsoShape1.CellsSRC(visSectionProp, intPropRow2, visCustPropsInvis).FormulaU = "1"

    Application.EndUndoScope UndoScopeID1, True



End Sub

Open in new window

0
Comment
Question by:-Polak
  • 3
  • 3
6 Comments
 
LVL 30

Accepted Solution

by:
Scott Helmers earned 500 total points
ID: 33691663
Let me answer you last question first -- yes, unfortunately, the macro recorder generates code that is very specific, so moving shapes around or selecting different shapes may prevent this code from working properly.

Consequently, we need to generalize a bit. The key problem with the code above is that it refers to each "invisible" cell by it's position in the shape data section of the shapesheet (that's the "intPropRow2" value). What we want to do is refer to the invisible cell based on the name of the field rather than a position in the shapesheet.

The code below does that. The two main subs ("HideShapeDataFields" and "ShowShapeDataFields") include the code to loop through each shape on each page. Within the main loop is a Call to a sub that does the actual work of hiding or showing a field (note that we pass the current shp and the name of the field we want to change). You can add as many Call statements as you have fields to hide/show -- just include one field name in quotes in each Call statement.

BTW, the reason that the called subs test for existence of a field before trying to hide it is that the main loop will cycle through every shape on every page -- including labels, graphics and any other random shapes that happen to be present. Because those shapes won't contain the data fields we're trying to alter, we need to prevent the code from producing an error if it tries to show/hide a non-existent field.

Regards,
Scott
 

Sub HideShapeDataFields()

' hide selected shape data fields



    Dim pg As Page

    Dim shp As Shape

    

    For Each pg In ActiveDocument.Pages

        For Each shp In pg.Shapes

            

            Call HideField(shp, "Telephone")

            Call HideField(shp, "Email")

            Call HideField(shp, "AnotherField1")

            Call HideField(shp, "AnotherField2")

            

        Next

    Next



End Sub

Private Sub HideField(shp As Visio.Shape, sField As String)

' if field exists, hide it



    If shp.CellExistsU("Prop." & sField, False) Then

        shp.CellsU("Prop." & sField & ".Invisible").Formula = True

    End If



End Sub

Sub ShowShapeDataFields()

' show selected shape data fields



    Dim pg As Page

    Dim shp As Shape

    

    For Each pg In ActiveDocument.Pages

        For Each shp In pg.Shapes

            

            Call ShowField(shp, "Telephone")

            Call ShowField(shp, "Email")

            Call ShowField(shp, "AnotherField1")

            Call ShowField(shp, "AnotherField2")

            

        Next

    Next



End Sub

Private Sub ShowField(shp As Visio.Shape, sField As String)

' if field exists, show it



    If shp.CellExistsU("Prop." & sField, False) Then

        shp.CellsU("Prop." & sField & ".Invisible").Formula = False

    End If



End Sub

Open in new window

0
 
LVL 1

Author Comment

by:-Polak
ID: 33702899
Am I using the "Label" of the field or the "Name" of the Field?
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 33703195
Good question -- you need to use the name.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:-Polak
ID: 33703264
Alright well i've gotten them to hide successfully, but they won't show up again? No debugger or anything just seems to not do anything when the ShowShapeDataFields macro is run.


Sub HideShapeDataFields()

' Hide Sensitive Employee Data



    Dim pg As Page

    Dim shp As Shape

    

    For Each pg In ActiveDocument.Pages

        For Each shp In pg.Shapes

            

            Call HideField(shp, "SeriesGrade")

            Call HideField(shp, "_VisDM_Reports_to")

            Call HideField(shp, "_VisDM_Supervisory_Code")

            Call HideField(shp, "_VisDM_FPL")

            Call HideField(shp, "_VisDM_Bargaining_Unit")

            Call HideField(shp, "_VisDM_Master_Record_Number")

            Call HideField(shp, "_VisDM_Funding")

            Call HideField(shp, "_VisDM_IPN")

            

        Next

    Next



End Sub

Private Sub HideField(shp As Visio.Shape, sField As String)

' if field exists, hide it



    If shp.CellExistsU("Prop." & sField, False) Then

        shp.CellsU("Prop." & sField & ".Invisible").Formula = True

    End If



End Sub

Sub ShowShapeDataFields()

' Show Sensitive Employee Data



    Dim pg As Page

    Dim shp As Shape

    

    For Each pg In ActiveDocument.Pages

        For Each shp In pg.Shapes

            

            Call HideField(shp, "SeriesGrade")

            Call HideField(shp, "_VisDM_Reports_to")

            Call HideField(shp, "_VisDM_Supervisory_Code")

            Call HideField(shp, "_VisDM_FPL")

            Call HideField(shp, "_VisDM_Bargaining_Unit")

            Call HideField(shp, "_VisDM_Master_Record_Number")

            Call HideField(shp, "_VisDM_Funding")

            Call HideField(shp, "_VisDM_IPN")

            

        Next

    Next



End Sub

Private Sub ShowField(shp As Visio.Shape, sField As String)

' if field exists, show it



    If shp.CellExistsU("Prop." & sField, False) Then

        shp.CellsU("Prop." & sField & ".Invisible").Formula = False

    End If



End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 33703457
It looks like a simple copy/paste problem. After pasting the customized call statements into ShowShapeDataFields, you need to change Call HideField... to Call ShowField...
0
 
LVL 1

Author Comment

by:-Polak
ID: 33704954
Thanks for the catch everything works great!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Have you ever created a custom Visio stencil – a collection of your own unique master shapes – and then created a drawing by dragging masters onto the drawing page? Have you then made changes to the stencil master and wondered why the shapes on the …
The ability to add structure to Visio diagrams using containers, lists and callouts is one of my favorite features in Visio 2010. In this article we’ll examine lists. We’ll explore containers and callouts in separate articles. Prior to reading th…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now