Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a macro that hides or shows Expressions in a chart depending on what options a user selects from an InputBox. It works fine but I need to know if it's possible to reference the expressions by name rather than by "Item(3)". My users may change the order of the Expressions in the chart and I need to make sure the proper Expressions are acted upon regardless of their order.
.
sub CasesorPounds
vSelection = ActiveDocument.Variables("vCasesPounds").GetContent.String
set vChart1 = ActiveDocument.GetSheetObject("CH04")
set vCP1 = vChart1.GetProperties
set vCP1Expression3 = vCP1.Expressions.Item(3).Item(0).Data.ExpressionData
set vCP1Expression4 = vCP1.Expressions.Item(4).Item(0).Data.ExpressionData
set vCP1Expression5 = vCP1.Expressions.Item(5).Item(0).Data.ExpressionData
set vCP1Expression6 = vCP1.Expressions.Item(6).Item(0).Data.ExpressionData
set vCP1Expression7 = vCP1.Expressions.Item(7).Item(0).Data.ExpressionData
set vCP1Expression8 = vCP1.Expressions.Item(8).Item(0).Data.ExpressionData
select case vSelection
case "Cases"
vCP1Expression3.Enable=true
vCP1Expression4.Enable=true
vCP1Expression5.Enable=false
vCP1Expression6.Enable=false
vCP1Expression7.Enable=true
vCP1Expression8.Enable=false
case "Pounds"
vCP1Expression3.Enable=false
vCP1Expression4.Enable=false
vCP1Expression5.Enable=true
vCP1Expression6.Enable=true
vCP1Expression7.Enable=false
vCP1Expression8.Enable=true
case "Both"
vCP1Expression3.Enable=true
vCP1Expression4.Enable=true
vCP1Expression5.Enable=true
vCP1Expression6.Enable=true
vCP1Expression7.Enable=true
vCP1Expression8.Enable=true
end select
vChart1.SetProperties vCP1
end sub
I don't believe there is a way to reference expressions by name. I agree that referencing them by order is severly limited. I've pretty much given up on expression based macros like this because of it.
Consider referring to the variable inside your expression to make the value different depending on your unit-of-measure variable.
For example, something like: if(vCasesPounds='Pounds' ,qty * wt_per_case, qty)
You might want to also reference the variable in an expression for the column title so the that the title says "Quantity in Pounds" or "Quantity in Cases" depending on the value of your variable.
Thanks Tim. I would've done it that way except that the users may want to see both Pounds and Cases at the same time. So hiding/showing expressions was the way I went.
You may already think about this (a bit crude and may affect performance). What if you create a procedure which loops thru the expression and disabling or enabling it as necessary. Something like
public sub EnableDisableExpression(pExpression, pFlag)
set vChart1 = ActiveDocument.GetSheetObject("CH01")
set vCP1 = vChart1.GetProperties
iNumExpr = vCP1.Expressions.count
for iCtr = 0 to iNumExpr - 1
if vCP1.Expressions.Item(iCtr).Item(0).Data.ExpressionData.Definition.v = pExpression then
vCP1.Expressions.Item(iCtr).Item(0).Data.ExpressionData.Enable = pFlag
exit for
end if
next
vChart1.SetProperties vCP1
end sub
Then on your if then statement you call the routing like
if Cases then
EnableDisableExpression "=sum(field1)", "false"
EnableDisableExpression "=sum(field2)", "true"
end if
if Pounds then
EnableDisableExpression "=sum(field1)", "true"
EnableDisableExpression "=sum(field1)", "false"
end if
i hope this helps.