• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

Macro Help Hiding ShapeData on an Organizational Chart

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
-Polak
Asked:
-Polak
  • 3
  • 3
1 Solution
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
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
 
-PolakAuthor Commented:
Am I using the "Label" of the field or the "Name" of the Field?
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Good question -- you need to use the name.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
-PolakAuthor Commented:
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
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
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
 
-PolakAuthor Commented:
Thanks for the catch everything works great!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now