¶ Pilcrow Sign in Excel/VBA

[RBartonSWW]

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.

 

[GlennUK]

I've never had this problem. What version of Excel are you using? Can you give the code that does the copying?

 

[RBartonSWW]

Excel 2002

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

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.

 

[RBartonSWW]

Can anybody help with this?? Its would be nice to sort it ASAP.


Cheers guys.

 

[GlennUK]

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

 

[RBartonSWW]

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]

Someone here must know how to fix this?!

 

[HalfAce]

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]

I think you helped me on it before in one of my other posts

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]

How have you set the properties of the textbox(s)?

Are they multiline?