Macro to Assign Formatting Values to Object Properties

[#1]

I have been working with the API Guide to develop a macro for automating the process of updating the formatting of objects (charts, tables, buttons, etc.) on our company's applications.  One specific and representative example of the trouble we are having is given here.  I am trying to assign a variable to the background color property of each button on a given sheet.  It looks like the code is in line with the API Guide's suggestions and examples regarding this, but the code does not work and an error keeps preventing its execution.  However, I can't seem to figure out what is wrong with the code, even after further research and changes.  Here is the code in its present form:

Sub ButtonColors()

  Dim buttonVars()

  index = 0

  Set vars = ActiveDocument.GetVariableDescriptions

  For i = 0 To vars.Count - 1

  Set tempVar = vars.Item(i)

  varName = Trim(tempVar.Name)

  ' Get name of formatting variable and add it to the buttonVars array

  If InStr(UCase(varName), "_VLAYOUT_BUTTON") <> 0 Then

  Redim Preserve buttonVars(i)

  buttonVars(i) = varName

  Set thisVar = ActiveDocument.Variables(varName)

  varContent = thisVar.GetRawContent

' msgBox(varName)

  End If

  Next

  For j = 0 To ActiveDocument.NoOfSheets - 1

        ActiveDocument.Sheets(j).Activate

        Set MySheet = ActiveDocument.GetSheet(j)

        Objects = MySheet.GetSheetObjects

        For k = lBound(Objects) To uBound(Objects)

        If Objects(k).GetObjectType = 5 Then ' Buttons

        Set prop = Objects(k).GetProperties ' Open the properties of the currently selected button

        prop.Layout.BkgColor.PrimaryCol.IsCalculated = True ' The background color uses a variable calculation

        prop.Layout.BkgColor.PrimaryCol.Col.v = "$(_vLayout_Button)" ' Set the calculated field to the button layout variable value

       Objects(k).SetProperties prop ' Set the background property for the currently selected button 

        End If

        Next

  Next

End Sub

[tamilarasu]

Hi Eugene,

I just did some small changes in your code. The below code will enable the calculated base color option for all the buttons in your application. But I am really not sure what you are trying to assign in the calculated base color. Do you have more than one color variable for each button? If you can create one small sample file (two sheets with button)  and explain us what is your requirement, It would be easy for us to guide you further.

Sub ButtonColors()

  Dim buttonVars()

  index = 0

  Set vars = ActiveDocument.GetVariableDescriptions

  For i = 0 To vars.Count - 1

  Set tempVar = vars.Item(i)

  varName = Trim(tempVar.Name)

  ' Get name of formatting variable and add it to the buttonVars array

  If InStr(UCase(varName), "_VLAYOUT_BUTTON") <> 0 Then

  Redim Preserve buttonVars(i)

  buttonVars(i) = varName

  Set thisVar = ActiveDocument.Variables(varName)

  varContent = thisVar.GetRawContent

'msgBox(varName)

  End If

  Next

  For j = 0 To ActiveDocument.NoOfSheets - 1

        ActiveDocument.Sheets(j).Activate

        Objects = ActiveDocument.ActiveSheet.GetSheetObjects

        For k = lBound(Objects) To uBound(Objects)

        If Objects(k).GetObjectType = 5 Then ' Buttons

        msgbox "Button"

        Set prop = Objects(k).GetProperties ' Open the properties of the currently selected button

        prop.BkgColor.PrimaryCol.IsCalculated = True ' The background color uses a variable calculation

      ' prop.BkgColor.PrimaryCol.Col.v = "$(_vLayout_Button)" ' Set the calculated field to the button layout variable value

     ' Kindly explain us this part

        Objects(k).SetProperties prop ' Set the background property for the currently selected button

        End If

        Next

Next

End Sub

[marcus_sommer]

If I look on this example from the APIGuide.qvw:

rem create new shortcut button - clear except locked

set newbutton = ActiveDocument.ActiveSheet.CreateButton

set prop = newbutton.GetProperties

prop.Text.v = "Clear"

prop.BkgColor.PrimaryCol.Col = RGB(78,99,15)

prop.BkgMode = 1

prop.Type = 1    

prop.ShortcutFunction = 4

newbutton.SetProperties prop

it seems that your ".v" on the end from property is wrong. Beside them - do you really need macros for all the formatting-stuff?

- Marcus