¶ Pilcrow Sign in Excel/VBA

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
I have a VBA userform that copies the contents of some textboxes in it onto the spreadsheet and each time it does it, it adds the ¶ Pilcrow Sign and it is also adding a " quote mark too.
The data in the boxes on the userform can be full or empty and it will still add these signs.

Does anybody know how to stop this from happening?

Thank you.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
I've never had this problem. What version of Excel are you using? Can you give the code that does the copying?
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
Excel 2002

Code:
Private Sub cmbUpdt_Click()

 Dim fCell As Range, Rng As Range
Dim LstRw As Long
LstRw = Cells(Rows.Count, 4).End(xlUp).Row
Set Rng = Range(Cells(1, 4), Cells(LstRw, 4))
Set fCell = Rng.Find(tbAcc, lookat:=xlWhole)
'where to find the info on the spreadsheet and the Userform

If Not fCell Is Nothing Then
    If Len(frmSearch.tbRtn) > 0 Then fCell(1, -1).Value = frmSearch.tbRtn.Text
    If Len(frmSearch.tbSnt) > 0 Then fCell(1, -2).Value = frmSearch.tbSnt.Text
    If Len(frmSearch.tbChsd) > 0 Then fCell(1, 3).Value = frmSearch.tbChsd.Text
    If Len(frmSearch.tbName) > 0 Then fCell(1, 2).Value = frmSearch.tbName.Text
    If Len(frmSearch.tbRef) > 0 Then fCell(1, 0).Value = frmSearch.tbRef.Text
    'If the contents of the Userform is different to the spreadsheet it will change the spreadsheet to match the Userform.

End If
End Sub

and also

Code:
Private Sub cmdAdd_Click()

' Assign LstRw as a data type Long
Dim LstRw As Long

' Determine the last row with any data and _
assign the variable LstRw to the row below that.
LstRw = Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, _
xlPrevious, False, False).Row + 1

' Enter textbox values to their respective cells.
With frmNew
Cells(LstRw, 4).Value = .tbAcc2.Text 'Column A
Cells(LstRw, 2).Value = .tbRtn2.Text 'Column B
Cells(LstRw, 1).Value = .tbSnt2.Text 'Column C
Cells(LstRw, 6).Value = .tbChsd2.Text 'Column D
Cells(LstRw, 5).Value = .tbName2.Text 'Column E
Cells(LstRw, 3).Value = .tbRef2.Text 'Column F
End With

End Sub


Both of them do it and I really don't understand it.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

Is it adding those characters to the contents of the cells, or just what is shown?
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
It's adding them to the contents of the cells, so I end up with, for example

"Mr Smith¶"¶

or

"Mr Smith"

in the cells when it should just be

Mr Smith

Very confusing and irritating.
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38

ADVERTISEMENT

Someone here must know how to fix this?!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hmm... That code looks very familiar, and I don't see why that would be happening, nor have I (yet) been able to make it do it for me.

I'll keep trying and see what I can see.
 

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
I think you helped me on it before in one of my other posts

:confused: This is irritating, there is no reason for it to do it, I could understand it if it only did it for empty textboxes on the userform as then I could understand it entering effectivly a 'null' character as there is nothing to enter, but because it does it when it is full aswell, I am quite confused.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,102
Office Version
  1. 365
Platform
  1. Windows
How have you set the properties of the textbox(s)?

Are they multiline?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,268
Messages
5,577,102
Members
412,768
Latest member
klig
Top