About Us

Jim May[_3_]

in article , Dave Peterson at
wrote on 8/21/08 1:32 PM:

Thanks Dave;
I think I'm beginning to sorta understand vba, after all these years AND
Especially for your input over that time.
Much appreciated,
Jim

Or you could just adjust the string when you plop it into the cell:

.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Value _

= Replace(.TextBox1.Text, vbCrLf, "")

Then you don't have to select the cell or work on the activecell.

JMay wrote:

Thanks Dave;

I made the changes as you suggested. For a while it still was not working
until I realized that the *&^^ screwy results were due to the "activecell"
location.

Final Code (with correction of this) is:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With Sheets("Sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = .TextBox1.Text
.Cells(Rows.Count, "A").End(xlUp).Select ' necessary to lock down
activecell
End With

With ActiveCell
.Value = Replace(.Value, vbCrLf, "")
End With

With Sheets("Sheet1").TextBox1
.Text = ""
.Activate
End With
Application.ScreenUpdating = True
End Sub

"Dave Peterson" wrote:

I'm betting that you have both the carriage return (chr(13) and linefeed
(chr(10)) in your string.

With ActiveCell
'xl2k+
'.value = replace(.value,vbcrlf,"")

'xl97
.Value = Application.Substitute(.Value, chr(13) & Chr(10), "")

'or
'.Value = Application.Substitute(.Value, vbcrlf,"")

End With

vbcrlf is the same as chr(13) & chr(10)
vblf is the same as chr(10)
vbcr is the same as chr(13)
vbnewline is OS dependent. On Wintel, it's vbcrlf. On Mac, it's vblf
(IIRC).

So if your code may run on a mac, you may want to use vbnewline. (But I
wouldn't trust me without testing!)

JMay wrote:

Testing the current LEN() of all my entries I'm getting 2 more than
desired.

I enter into textbox1 good (prssing the return key)

With
EnterKeyBehaviour = True
Multiline = True
Word Wrap = True

=Len() against cell containg good = 6, not 4

"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = .TextBox1.Text
End With

With ActiveCell

.Value = Application.WorksheetFunction.Substitute(.Value, Chr(13),
"")
End With

With Sheets("Sheet1").TextBox1
.Text = ""
.Activate
End With
End Sub

--
__________________________________
HTH

Bob

"JMay" wrote in message
...
Above occurs on Line 4 below Activecell.value = .......

My intent is to clean up the extra characters (return type) being entered
into my cells. Perhaps there is a better way. But lost here...
TIA

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value =
Sheets("Sheet1").TextBox1.Text
ActiveCell.Value = Application.WorksheetFunction.Substitute(ActiveCel l,
char(13), "")
With TextBox1
.Text = ""
.Activate
End With
End Sub

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Microsoft Visual Basic: Compile error: Sum or Function not defined	Dmitry	Excel Worksheet Functions	12	April 3rd 06 07:28 AM
Microsoft Visual Basic: Compile error: Sum or Function not defined	Dmitry	Excel Programming	13	April 3rd 06 07:26 AM
Help With - Compile Error: Sub or Function Not Defined	MWS	Excel Programming	2	March 23rd 06 06:51 PM
"Compile error: sub or function not defined"	Joe	Excel Discussion (Misc queries)	4	January 30th 06 08:19 PM
VBAProject name compile error, not defined at compile time	Matthew Dodds	Excel Programming	1	December 13th 05 07:17 PM

Menu

About Us