How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

**stuu3270** · 01-10-2013, 01:44 PM

Hello again,

I have designed a userform that populates a spreadsheet.

My form uses text boxes which I have set to allow the enter key and multi line formatting (for addresses or paragraphs of text).
My text box, for example, is called txtAddress and this text box populates cells Q9:Q999 on my spreadsheet.

I have seen answers with a few lines of code but I don't know how to adapt the code or where to put it. Does it go in the cells or in the coding of the userform somewhere?

Can somebody help with the example given?

Thanks,
Stu

**Norie** · 01-10-2013, 01:46 PM

Stu

How are you currently putting the data from the userform on the worksheet?

Do you still want to display the data in multiple lines as it was on the form?

**stuu3270** · 01-10-2013, 01:55 PM

Yes. I would like fo rthe cell to reflect the layout from within the userform text box. So this in the text box:

address line 1
address line 2
address line 3

will be replicated in the cell it populates.

I currently have something like this in my userform code:

ActiveCell.Offset(0, 3) = txtLocation.Value
ActiveCell.Offset(0, 5) = txtReference.Value
ActiveCell.Offset(0, 11) = txtComments.Value

**Norie** · 01-10-2013, 01:58 PM

Try setting word wrap on in the cells were the multiline data is going.

**stuu3270** · 01-10-2013, 02:06 PM

I have done that. But I still get the small rectangle with a question mark inside at the end of every line in the cell.

**Norie** · 01-10-2013, 02:11 PM

I can't replicate that, whatever I try.

Can you attach a sample workbook?

**stuu3270** · 01-10-2013, 02:14 PM

Thanks Norie,

Actually, I just did this in Excel 2010 at home and it works. The problem is at work on Excel 2003 where this doesn't work. I use the same settings but the symbol appears.
Why is this and how do I get rid?

Stu

**Norie** · 01-10-2013, 02:17 PM

Stu

Long time since I used 2003 but I'm pretty sure that turning on word wrap worked there too.

How exactly were you trying it in 2003? Where/when in the code did you turn on word wrap?

**stuu3270** · 01-10-2013, 02:19 PM

In the userform I highlighted the text box, set to 'true' the 'enter key behaviour' and the 'multi line' option.
In the data cells of the spreadsheet I highlighted the relevant cells/ column and in format cells I checked 'word wrap'.

**Norie** · 01-10-2013, 02:22 PM

Did you try setting word wrap using code?

Please Login or Register  to view this content.

**stuu3270** · 01-10-2013, 02:25 PM

I can try tomorrow. Where exactly do I put the code?

**Norie** · 01-10-2013, 02:29 PM

It would go where the code you already have for putting the data on the worksheet is.

For example.

Please Login or Register  to view this content.

**stuu3270** · 01-11-2013, 03:52 PM

Doesn't work.

Here is an excerpt from my user form to make sure I got it right:

Please Login or Register  to view this content.

**stuu3270** · 01-11-2013, 04:05 PM

I also just found this but don't know where to put it:

Please Login or Register  to view this content.

Would I change A1 to suit my range in column Q (Q5:Q999)?

Stu

**Norie** · 01-11-2013, 04:24 PM

Stu

That's a worksheet formula, it could be converted to VBA but if you want to keep the data on multilines I'm not sure it's a good idea.

Could you upload a sample workbook?

**stuu3270** · 01-11-2013, 04:37 PM

I can only upload from home. At work at moment. I would prefer a VBA code to put into the userform side of things.

Will upload later. Thanks
Stu

**stuu3270** · 01-12-2013, 11:03 AM

I have sent the file to my home email address to upload it here. The thing is it works because I have the latest version of Excel. I can only replicate the problem in the Excel version at work. It is 2003 I think, maybe 2007.

I may sent the file anyway in case you can still help.

**stuu3270** · 01-12-2013, 11:31 AM

Here is what I am working on. As I say, it works in the latest version but not in the earlier version I am using.
I am going out of my mind trying to find a fix to this!!

How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

LinkBack

Thread Tools

Rate This Thread

Display

How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Re: How Do I Remove The Carriage Return Symbol From Cell Populated By A UserForm TextBox?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions