Trouble with writing writing values to cells (#Name? error)

[Bryant]

I have made a process mapping template in which a user enters both the
name of the process being mapped (prop.processname) as well as the
interviewee (prop.interviewee) within a page title shape. These 2
shape data field values are written to the page's shape data (using
setf).

All of my customized shapes that are subsequently dropped on the page
will then reference this information from the page (using "Guarded"
formulas to prevent accidental user overwrite), effectively tagging
all shapes as being a part of a particular process and interviewee.

Now I wish to have all of my separate process maps exist on one visio
page but in order to prevent all dropped shapes from referencing the
information on the new visio page, I will need to write a macro that
will cycle through all shapes on their current page, replacing the
existing formula with it's current static value.

Here is what I have come up with so far:

Sub MakeShapeDataStatic()

'This macro cycles through every shape on a page looking for the shape
data fields "prop.processname" and "prop.interviewee"
'When found, the macro replaces the existing formula with it's current
"Static" value.


Dim pag As Visio.Page
Dim PageTitle As Visio.Shape
Dim ProcessNameCell As Cell
Dim IntervieweeCell As Cell
Dim processname As String
Dim interviewee As String
Dim shp As Visio.Shape


'Must have Page Title shape selected for this to work

If Visio.ActiveWindow.Selection.PrimaryItem.CellExists
("prop.processname", Visio.visExistsAnywhere) Then

Set PageTitle = Visio.ActiveWindow.Selection.PrimaryItem
Set ProcessNameCell = PageTitle.Cells("prop.ProcessName")
processname = PageTitle.Cells("prop.processname").ResultStr
(Visio.VisUnitCodes.visNoCast)
Set IntervieweeCell = PageTitle.Cells("prop.interviewee")
interviewee = PageTitle.Cells("prop.interviewee").ResultStr
(Visio.VisUnitCodes.visNoCast)

End If

Set Page = Visio.ActivePage

For Each shp In Page.shapes

If shp.CellExists("prop.ProcessName", Visio.visExistsAnywhere)
Then

'Must use formulaforce because current formula in cell is guarded

shp.Cells("prop.ProcessName").FormulaForce =
processname

'I keep getting #Name? error here...it is supposed to force the static
value of Processname into the cell of the current shape

End If

If shp.CellExists("prop.interviewee", Visio.visExistsAnywhere)
Then
shp.Cells("prop.interviewee").FormulaForce = interviewee
End If

Next shp

End Sub

Any and all help is welcomed and appreciated!

Thank you,
Bryant Lemieux

 

[Andy]

All your missing are some extra double quotes to assign a string to
the formula, otherwise it thinks your giving it a cell name/formula
and hence the error.

shp.Cells("prop.ProcessName").FormulaForce = chr(34) & processname &
chr(34)

 

[Bryant]

That did the trick! Thanks so much for taking the time to read a very
long posting.

-Bryant