Compile error - Sub Function not defined
 

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

Compile error - Sub Function not defined
 

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

Compile error - Sub Function not defined
 

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

Compile error - Sub Function not defined
 

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


--

Dave Peterson

Compile error - Sub Function not defined
 

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