Assign variable (string) to Value column of User Defined Cell

[jennifer.mezger]

I am having difficulty assigning a string to the Value Column of a
User Defined Cell. I am trying to create a MACRO which will ask the
user to input text information into a form and then add that text to
the DOCUMENT level User Defined Cell (Value Column). I know the
column can take string because I can enter the text manually
("string"). I am setting the value of the form's text field to a
variable (As String) and then trying to set the cell's formula to the
variable. I get a runtime error saying "#NAME?". Here is my code:

Dim clienttext As String
Dim projecttext As String
Dim UndoScopeID1 As Long

clienttext = ClientTextBox.Text
projecttext = ProjectTextBox.Text

'Insert row and enter data
UndoScopeID1 = Application.BeginUndoScope("Insert Row")
Application.ActiveWindow.Shape.AddRow visSectionUser, 0,
visTagDefault
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 2,
visUserValue).FormulaForceU = "0"
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 2,
visUserPrompt).FormulaForceU = """"""
Application.EndUndoScope UndoScopeID1, True

'Client Name
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 2,
visUserValue).RowNameU = "ClientName"
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 2,
visUserValue).FormulaU = clienttext
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 2,
visUserPrompt).FormulaU = ""

'Insert Row and enter data
UndoScopeID1 = Application.BeginUndoScope("Insert Row")
Application.ActiveWindow.Shape.AddRow visSectionUser, 0,
visTagDefault
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 3,
visUserValue).FormulaForceU = "0"
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 3,
visUserPrompt).FormulaForceU = """"""
Application.EndUndoScope UndoScopeID1, True

'Project Name
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 3,
visUserValue).RowNameU = "ProjectName"
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 3,
visUserValue).FormulaU = projecttext
Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 3,
visUserPrompt).FormulaU = ""

Any ideas on how to get this to work?

 

[Al Edlund]

I'd probably do it a little different
As a couple of guiding points
Don't addrows/sections without testing to see if they already exist
Document (and pages) have there own shapesheets, you can see me
addressing the document sheet
Make sure you're in the correct row before changing cells

al


Option Explicit

Public Sub loadProjectName()

Dim clienttext As String
Dim projecttext As String
Dim UndoScopeID1 As Long
Dim intNewRow As Integer
Dim visCell As Visio.Cell
On Error GoTo ErrPrompt

Dim docCurrent As Visio.Document
Set docCurrent = Application.ActiveDocument

Dim shpDocSheet As Visio.Shape
Set shpDocSheet = docCurrent.DocumentSheet


' clienttext = ClientTextBox.Text
' projecttext = ProjectTextBox.Text

clienttext = "client"
projecttext = "project"

'Insert row and enter data
'test to see if the cell already exists
If shpDocSheet.CellExists("user.clientname", False) = False Then
UndoScopeID1 = Application.BeginUndoScope("Insert Row")
intNewRow = shpDocSheet.AddNamedRow(visSectionUser, "clientname",
visTagDefault)
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserValue).FormulaU = clienttext
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserPrompt).FormulaU = """"
Application.EndUndoScope UndoScopeID1, True
Else
intNewRow = shpDocSheet.CellsRowIndex("user.clientname")
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserValue).FormulaU = """" & clienttext & """"
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserPrompt).FormulaU = ""
End If

If shpDocSheet.CellExists("user.projectname", False) = False Then
UndoScopeID1 = Application.BeginUndoScope("Insert Row")
intNewRow = shpDocSheet.AddNamedRow(visSectionUser, "projectname",
visTagDefault)
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserValue).FormulaU = projecttext
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserPrompt).FormulaU = """"
Application.EndUndoScope UndoScopeID1, True
Else
intNewRow = shpDocSheet.CellsRowIndex("user.projectname")
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserValue).FormulaU = """" & projecttext & """"
shpDocSheet.CellsSRC(visSectionUser, intNewRow,
visUserPrompt).FormulaU = ""
End If


Exit Sub

ErrPrompt:

MsgBox Err.Description

End Sub

 

[JuneTheSecond]

Hi, Jennifer.

An ieda is to wrap text in double quotation.
The text that is not mathematical formula
must be double quoted.
The text in the cell must look like "TEXT",
but never like TEXT.

......FormulaU = DQ(clienttext)
......


Private Function DQ(text As String)
DQ = Chr(34) & text & Chr(34)
End Function

 

[jennifer.mezger]

All great comments, thanks Al. I am still receiving an error message:
"#NAME?". Is this error specific to my visio file? Any ideas what
this error means?

Jen

 

[jennifer.mezger]

Hi, Jennifer.

An ieda is to wrap text in double quotation.
The text that is not mathematical formula
must be double quoted.
The text in the cell must look like "TEXT",
but never like  TEXT.

.....FormulaU = DQ(clienttext)
.....

Private Function DQ(text As String)
    DQ = Chr(34) & text & Chr(34)
End Function

Thank you SO much for all your help - the function removed the error I
was receiving. I can now stop spinning my wheels and move on
Appreciate all the help!

Jen