Enter key text symbol

  • Thread starter Thread starter Fiona
  • Start date Start date
F

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
 
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
 
Thanks Dianne,

Well that looks easy :o),
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 ?
 
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.
 
Back
Top