Enter key text symbol

[Fiona]

I have a form with a multiline textbox which has (EnterKeyBehavior
=True) property.

When the ok button is clicked on the form, the contents of the textbox
is placed into a range on a worksheet. Problem is the ascii symbol for
the enter key, it is displayed at the end of every line.

Is there anything I can do to stop this ?

Private Sub CmdOK_Click()
ActiveSheet.Range("Notes") = TxtNotes.Text

Unload Me
End Sub

 

[Dianne]

Private Sub CmdOK_Click()
Dim strText As String
strText = TxtNotes.Text
strText = Application.WorksheetFunction.Substitute(strText, vbCr,
"")
ActiveSheet.Range("Notes") = strText
Unload Me
End Sub

 

[Fiona]

Thanks Dianne,

Well that looks easy ),
I didn't even know there was an "Application.WorksheetFunction.Substitute"

Makes you wonder how many ways it may be possible to do the same ?

 

[Dianne]

Fiona,

Application.WorksheetFunction allows you to access a number of the
functions that you use in your Excel worksheets, like VLOOKUP or
SUBSTITUTE. Sometimes the worksheet function is all you need. For a list
of the worksheet functions you can use in VBA, check VBA help.

The fun thing about this newsgroup is that you find lots of different
ways to do the same thing! I find myself frequently jolted out of my VBA
complacency -- "What? You can do that with one line of code instead of
20? Arrghhh!" <g> I've learned a lot here.