Use VBA function to populate a fixed list

[IrkenInvader]

My goal is to call code and have it populate options in a shape data Fixed List.

I made a function on the VBA side that returns a string like "opt1;opt2;opt3".  So far I've tried setting the format column to CALLTHIS("ThisDocument.GetMyOpts",) with GetMyOpts being a public sub.

I am mostly looking to see if I might have some syntax issues or calling the function in the wrong way...or if this is just not the correct approach at all.

Thanks for any input!

[wapperdude]

Need more info...
1) what does your code look like?
2) when should this run?
3) what is the desired triggering mechanism?
4) does it need to be generally available or just to a specific file?
5) are there security restrictions?

[IrkenInvader]

My code is like this

Code Select Expand

Public Function GetMyOpts() As String
  GetMyOpts = "opt1;opt2;opt3"
End Function
And here is my attempt to use this function in the ShapeSheet -> Shape Data window -> Format column

Code Select Expand

=CALLTHIS("ThisDocument.GetMyOpts",)
Then I have the Data ribbon -> Shape Data Window turned on

If i click my shape I can see the Fixed List dropdown but the only option there is FALSE

Not picky on when it triggers, recalculating each time you click the shape or only once - both should be fine.

Working in this file only, no security concerns.

[Yacine]

Not sure if I really understood, but here's my try.

You want to populate a format cell with the result of a macro. The problem is, you cannot make that cell call the macro and hold the result at the same time.
You need to choose another trigger and calling cell - that could be anything, even an automatic update as follows.
Open the shapesheet of the shape, add the user section, add a user row.
In the user row add your formula:  =CALLTHIS("ThisDocument.GetMyOpts",)

Modify the routine as follows:

Code Select Expand

Public Sub GetMyOpts(shp as shape)
  GetMyOpts = "opt1;opt2;opt3"
  shp.Cells("prop.A.Format").Formula = chr(34) & GetMyOpts & chr(34)
End sub

The trick is that you can't just put in a function and expect to get its result in the cell. That would be what a UDF (user defined function) would be in Excel. What you can do is call a sub and pass the shape itself as argument.
This routine can now do any modification to the calling shape.
Note, if you want to re-use the sub for different rows, you can add more parameters (check the online help).