¶ 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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
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,685

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,456
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,343
Office Version
  1. 365
Platform
  1. Windows
How have you set the properties of the textbox(s)?

Are they multiline?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,544
Messages
5,832,372
Members
430,128
Latest member
ojl987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top