VB Macro Loop to Create Field Trigger Set Variable Action

    This has been created with help from multiple questions and answers on the forum and some extra vb searches. Hopefully, others will be able to adapt this for other purposes as required.

     

    Requirements

    • I needed to create set variable actions as field triggers for a complex QVD creator user interface I have made.
    • I wanted to be able to have the creation and deletion of these triggers automated due to the large number of fields.
    • I also wanted to have both OnChange and OnSelect triggers for these fields.
    • The variable values are the possible values of the fields separated by hashes. This is used by my QVD creator load script (that's for another day).

     

    My real field names have been replaced with generic text for this posting. In this example the field names I am using have a similar naming structure of LIST_GROUP1_NAME1. The variables have a similar naming structure of vd_SELECTIONS_GROUP1_NAME1. I wanted to be able to have multiple arrays to loop through these levels.

     

    sub create_field_triggers

     

    'turn on msgboxes for testing

    v_msgboxes = 0

     

    'systems

    array_systems = Array("GROUP1","GROUP2","GROUP3","GROUP4")

     

    For s = lbound(array_systems) to ubound(array_systems)

     

    'fields

    array_fields = Array("NAME1","NAME2","NAME3","NAME4","NAME5","NAME6","NAME7","NAME8","NAME9","NAME10")

     

    For f = lbound(array_fields) to ubound(array_fields)

     

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) end if

     

    'remove existing onchange actions

    set fld=ActiveDocument.GetField("LIST_" & array_systems(s) & "_"& array_fields(f)) 

    set prop = fld.GetProperties 

    set actions=prop.OnChangeActionItems 

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Delete OnChangeActionItems = " & actions.count end if

    for i=actions.Count-1 to 0 step -1

    actions.removeat(i) 

    fld.setproperties prop

    next  

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Delete OnChangeActionItems = " & actions.count end if

     

    'remove existing onselect actions

    set actions=prop.OnSelectActionItems 

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Delete OnSelectActionItems = " & actions.count end if

    for i=actions.Count-1 to 0 step -1

    actions.removeat(i)

    fld.setproperties prop 

    next

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Delete OnSelectActionItems = " & actions.count end if

     

    'create onchange actions

    set actions=prop.OnChangeActionItems

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Add OnChangeActionItems = " & actions.count end if

    actions.Add

    i = prop.OnChangeActionItems.count-1

    actions(i).Type = "31"

    actions(i).Parameters.add

    actions(i).Parameters(0).v = "vd_SELECTIONS_" & array_systems(s) & "_"& array_fields(f)

    actions(i).Parameters.add

    actions(i).Parameters(1).v = "='#'&CONCAT(DISTINCT LIST_" & array_systems(s) & "_" & array_fields(f) & ",'# #')&'#'"

    fld.SetProperties prop

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Add OnChangeActionItems = " & actions.count end if

     

    'create onselect actions

    set actions=prop.OnSelectActionItems

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Add OnSelectActionItems = " & actions.count end if

    actions.Add

    i = prop.OnSelectActionItems.count-1

    actions(i).Type = "31"

    actions(i).Parameters.add

    actions(i).Parameters(0).v = "vd_SELECTIONS_" & array_systems(s) & "_"& array_fields(f)

    actions(i).Parameters.add

    actions(i).Parameters(1).v = "='#'&CONCAT(DISTINCT LIST_" & array_systems(s) & "_" & array_fields(f) & ",'# #')&'#'"

    fld.SetProperties prop

    if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Add OnSelectActionItems = " & actions.count end if

     

    next

     

    next

     

    end sub

     

    Other actions types can be created instead. I was using the below script to check the action type codes for actions that already exist on a field.

     

    sub check_trigger_types

     

    'field name to be checked

    set fld=ActiveDocument.GetField("LIST_GROUP1_NAME1")

     

    set fp = fld.GetProperties

    set actions=fp.OnSelectActionItems

     

    for i=0 to actions.Count-1

    msgbox "ID: " & i & chr(10) & "Type: " & actions.item(i).Type & chr(10) & "Parameter 1: "& actions.item(i).Parameters(0).v & chr(10) & "Parameter 2: "& actions.item(i).Parameters(1).v

    next

     

     

    end sub