topic Re: Macro to Assign Formatting Values to Object Properties in QlikView

Macro to Assign Formatting Values to Object Properties

Tue, 29 Nov 2016 16:06:37 GMT

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

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

End Sub

Re: Macro to Assign Formatting Values to Object Properties

tamilarasu — Tue, 29 Nov 2016 18:05:34 GMT

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

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

End Sub

Re: Macro to Assign Formatting Values to Object Properties

marcus_sommer — Wed, 30 Nov 2016 12:21:39 GMT

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