vbCrLf question.

I have a text box that contains an address value such as

Brian Wilson
123 Harper Way
Some city, Florida
33333

I want to take this this value and put it into an Excel cell as is. Right now when I import this value into Excel it look like this:

BrianWilson123HarperWaySomeCity,Florida33333

How do I get around this?

Maybe:
"Brian Wilson" & vbcrlf & "123 Harper Way" & vbcrlf & "SomeCity, Florida" & vbcrlf "33333"

Vbcrlf can be used for putting information onto the next line
You can also use vbnewline which does the same.

you'll probably also want to set the text wrap property of the cell

Phinds:
As soon as a vbCR, or vbLF or a vbCrLf is used the wrap text property is automatically set, but good thinking.

Geudens:
You don't want to use vbCrLf the vbCr part causes a little rectangle to be displayed in its place. However, you can use vbLf instead of vbCrLf.

Yellowman:
I find it strange that it is placing the lines like that in excel. In my experiences you usually have to take all the vbCr out of the string transfered to the excel cell (thereby leaving only vbLf and text). What version of excel are you using and what code are you using to tranfer the text from the textbox to excel?

I am having a problem parsing some text and putting it into a spreadsheet. I have a text box that a user is going to put data in. Sometimes that data in the text box is going to be single line text, sometimes the data is going to span multiple lines. Then when I click a button the text from the text box is supposed to be added to one cell of the spread sheet. It works partially. The text is added to my spreadsheet, but it won't recognize mutiple line input. Every thing is always put in as single line input. Example of what is does

Text1: aaaa
bbbbb

Excel: aaaa bbbbb

I want the excel cell to look identical to the text in Text1.
I have heard that I could use vbCrLf, but I don't know how to since the text in the text box will never be the same. Is there a way to code it so that each letter is parsed until an "end of line" character is reached then activate vbCrLf?


Here is the code so far.

If (Headertxt2.Enabled = True) Then
Grid1.ActiveSheet.Cells(3, 1).Select
Grid1.Cells(3, 1).Value = Headertxt2.Value
Grid1.Cells(3, 1).Font.Size = 12
Grid1.Cells(3, 1).Font.Bold = True
Grid1.Cells(3, 1).Font.Name = CommonDialog1.FontName
Headertxt2.Value = Null
Headertxt2.Enabled = False
Headertxt2.BackColor = 12632256
End If

I think that simply changing it to print Headertxt2.Text (instead of .Value) should do it for you.

That didn't work. I got an error that say: "You can't reference a property or method for a control unless the control has focus". How do I get rid of this?

That didn't work. I got an error that say: "You can't reference a property or method for a control unless the control has focus". How do I get rid of this?

I don't think I've ever seen that error in Excel. I've seen something similar in Access, I think.

Maybe try putting the form name before the name of the textbox, such as:


Debug.Print Form1.Headertxt.Text

I am in Access. I guess I wasn't clear. I have built a custom form in Access. In this form I have an embedded Excel sheet that is going to display my data from my query. I just don't know how to get some of the excel cells to diplay data as previously descibed;-)

I am in Access. I guess I wasn't clear. I have built a custom form in Access. In this form I have an embedded Excel sheet that is going to display my data from my query. I just don't know how to get some of the excel cells to diplay data as previously descibed;-)

Try adding:

Cells(3,1).WrapText = True

I don't know how embedding objects work in Access, but if you futz with "Cells(3,1)" by pre-sizing the Rows and Columns (so it fits the size of the TextBox in you Form, the Wrap Text should take care of it any vbCr Stuff...

(And hey, thanks to all the above comments that told me what I had to do to get rid of those annoying squares in my custom Comments...

It was annoying to me, because vbCrLf works fine in a dialog box, but not when you're using it for a Comment...)

--Dodd

I get still get an error. It says that the excel grid doesn't support this property or method and 'wordwrap' is highlighted.

I get still get an error. It says that the excel grid doesn't support this property or method and 'wordwrap' is highlighted.

Eep. Dunno what to tell you... Like I said, I'm not well versed in embedded Excel situations... Sorry sir...

--Dodd

I used this to transfer a multiple line textbox to an embedded Excel sheet in Access.

Dim strText As String
Text1.SetFocus
strText = Text1.Text
'Remove vbCr leaving only the vbLF
strText = Replace(strText, vbCr, "")
OLE1.Verb = -1
OLE1.Action = 7
OLE1.Object.Application.Cells(1, 1).Value = strText

You may or may not be able to use the Replace function. If you can't I'll help you create a similiar function to remove the vbCr.

I can't get your code to run as is because Access VB doesn't recognize the Action method. I also get "you cannot reference a property or method for a control unless the control has focus" if I use .text intead of .value so I am always forced to use .value to get my code to work. Below is what I have (and it runs) but it still doesn't put multiple lines of data in one excel cell.

Dim strText As String
If (Headertxt2.Enabled = True) Then
Grid1.ActiveSheet.Cells(3, 1).Select
strText = Headertxt2.Value
strText = Replace(strText, vbCr, "")
Grid1.Cells(3, 1).Value = strText
Grid1.Cells(3, 1).Font.Size = 12
Grid1.Cells(3, 1).Font.Bold = True
Grid1.Cells(3, 1).Font.Name = CommonDialog1.FontName
Headertxt2.Value = Null
Headertxt2.Enabled = False
Headertxt2.BackColor = 12632256

End If


Man, this is alot harder than I expected. Its always the little stuff the trips me up;-)

What about creating a string variable and, in the Textbox's Change event, set that variable = to the Textbox.Text. You know that you should have access to the textbox in its change event. This way, in the button_click event, you can just refer to your string variable rather than referring to the Textbox control.

I can't get your code to run as is because Access VB doesn't recognize the Action method.
The Action method is part of the Access VBA in Access 2000.
What version of Access are you using?

I am using Access 2000.
What does the Action and Verb do?