UserForm; Textbox; Multiline; NonPrintable Characters
 

I've created a userform in Excel 97 with a text box
The properties of the text box include:
Multiline = True
Wordwrap = True

When entering data in the text box I press Ctrl & Enter to start a new line

When the data is transferred to the spreadsheet it includes non-printable
characters, denoted by small square boxes.

I can manually delete these characters but would like to find a more elegant
way of doing so

Can you suggest some code that I could use

Regards & TIA

Jim Burton

UserForm; Textbox; Multiline; NonPrintable Characters
 

Hello
Worksheets("Sheet1").range("A1").Value = Application.Substitute(TextBox1,
Chr$(13&),"")
HTH
Cordially
Pascal

"sa3214 @eclipse.co.uk" <jimburton<REMOVE a écrit dans le message de
...
I've created a userform in Excel 97 with a text box
The properties of the text box include:
Multiline = True
Wordwrap = True

When entering data in the text box I press Ctrl & Enter to start a new
line

When the data is transferred to the spreadsheet it includes non-printable
characters, denoted by small square boxes.

I can manually delete these characters but would like to find a more
elegant
way of doing so

Can you suggest some code that I could use

Regards & TIA

Jim Burton

UserForm; Textbox; Multiline; NonPrintable Characters
 

I've found that Ctrl & Enter is inserting Chr(10) and Chr(13) into the text
string
I have used the following code to edit the cell after transferring the
string.

.Cells(EntryRow, 9).Replace What:=Chr(13), Replacement:=""

It works ... but is there a better way of creating new lines in a textbox
and hence in the cell


"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
I've created a userform in Excel 97 with a text box
The properties of the text box include:
Multiline = True
Wordwrap = True

When entering data in the text box I press Ctrl & Enter to start a new
line

When the data is transferred to the spreadsheet it includes non-printable
characters, denoted by small square boxes.

I can manually delete these characters but would like to find a more
elegant way of doing so

Can you suggest some code that I could use

Regards & TIA

Jim Burton

UserForm; Textbox; Multiline; NonPrintable Characters
 

Thanks Pascal,

Much more elegant than the way I was doing it

Regards
Jim

"papou" wrote in message
...
Hello
Worksheets("Sheet1").range("A1").Value = Application.Substitute(TextBox1,
Chr$(13&),"")
HTH
Cordially
Pascal

"sa3214 @eclipse.co.uk" <jimburton<REMOVE a écrit dans le message de
...
I've created a userform in Excel 97 with a text box
The properties of the text box include:
Multiline = True
Wordwrap = True

When entering data in the text box I press Ctrl & Enter to start a new
line

When the data is transferred to the spreadsheet it includes non-printable
characters, denoted by small square boxes.

I can manually delete these characters but would like to find a more
elegant
way of doing so

Can you suggest some code that I could use

Regards & TIA

Jim Burton