Inserting Linebreak into Cells.Forumla

Started by lilbeanbear, September 27, 2017, 09:13:33 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

lilbeanbear

Sorry if I posted this in the wrong area.

Im building an office seating chart (giant waste of time, lol) and im trying to manipulate the "comment" field in the shape data of selected shapes. Instead of going into each shape and adding the "Prop._VisDMwhatever" to display on mouseover, I wanted to do it with a macro.

Ive done all the googling on this issue and havent found anyone trying to add Prop._VisDMwhatever variables with linebreaks into the comment line programatically.

The code below works how I want it to without the linebreaks:

///

Sub testbear()

Dim A As String, B As String, C As String, D As String
Dim Space As String

Dim sel As Selection
Dim selsh As Shape
Set sel = ActiveWindow.Selection
For x = 1 To sel.Count
Set selsh = sel(x)

A = selsh.Cells("Prop._VisDM_DisplayName").Formula
B = selsh.Cells("Prop._VisDM_Title").Formula
C = selsh.Cells("Prop._VisDM_Department").Formula
D = selsh.Cells("Prop._VisDM_EmailAddress").Formula
Space = A & B & C & D

Debug.Print Space
selsh.CellsU("comment").FormulaForce = Space
Next x
End Sub

///

I want to insert CHR(13), vbNewLine, vbCrLf, or any other type of line break in between my A,B,C,D variables inside of the "Space" var. Every time I try to concatenate a VB linebreak constant or CHAR(13) into the "Space" variable I get a "sub or function not defined" error. When I add the VB linebreak constant or CHAR(13) into its own string, I get a "Run-Time error -2032464666". I saw other posts for the same runtime on this forum, but they werent applicable.

Any idea how I can get linebreaks in between the A, B, C, D variables contained in the Space variable?

Any help from you guys would be awesome. Long time lurker, first time poster. :3

Surrogate

try add commas
selsh.CellsU("comment").FormulaForce = chr(34) & Space & chr(34)

lilbeanbear

I got runtime error -2032466907 with this code:

///

Sub testbear()

Dim A As String, B As String, C As String, D As String
Dim Special As String
Dim Space As String

Dim sel As Selection
Dim selsh As Shape
Set sel = ActiveWindow.Selection ' create "selection"
For x = 1 To sel.Count           ' iterate all shapes (from first to last) in selection
Set selsh = sel(x)               ' set current selected shape

Special = "CHAR(13)"

A = selsh.Cells("Prop._VisDM_DisplayName").Formula
B = selsh.Cells("Prop._VisDM_Title").Formula
C = selsh.Cells("Prop._VisDM_Department").Formula
D = selsh.Cells("Prop._VisDM_EmailAddress").Formula
Space = A & Special & B & C & D

Debug.Print Space
selsh.CellsU("comment").FormulaForce = Chr(34) & Space & Chr(34)
Next x
End Sub

///

And, I tried giving the "Special" variable single, double, and triple doublequotes, which all resulted in the same runtime error.

Surrogate

i dont know what data contain in these Prop._xxx cell ! May be some of shapes haven't one of these cells ?
I try simple code and it works
Sub tst()
Dim selsh As Shape, space As String
Set selsh = ActiveWindow.Selection.PrimaryItem
A = "First row"
B = "Second row"
space = A & Chr(13) & B
Debug.Print space
selsh.CellsU("comment").FormulaForceU = Chr(34) & space & Chr(34)
End Sub

lilbeanbear

The data in each of the Prop._VisDM* objects is just regular strings. Which is why the code works fine when there is no linebreak involved. Also, the data should be handled like a regular string, considering I defined A,B,C,D as plain strings. So when the code iterates through the shapedata, it should all be converted into a regular string.

Im wondering if theres an issue with how im iterating through the shape data thats causing this problem instead.

Oh VB... Y u do dis @_@

wapperdude

The issue would seem to be your setting of A, B, C, and D.  You should use Resultstr instead of Formula.  See https://msdn.microsoft.com/en-us/vba/visio-vba/articles/cell-resultstr-property-visio

Wapperdude
Visio 2019 Pro