Trying to improving existing code (portion of)

[Cameron]

Hi all,
Have the following code(portion of) and would like to hear of any
suggestions to making it more efficient.
TIA
Cameron
============================================================================
========
'TextBox3.Text has to have some value before getting to this point, so
include it
' in the ActiveCell.Value now.
ActiveCell.Value = TextBox3.Text
'Check if the remaining TextBoxes have any value and collate.
If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text = ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text

If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox5.Text
If TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox6.Text

If TextBox4.Text <> "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text & vbLf & _
TextBox5.Text

If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text & vbLf & _
TextBox6.Text

If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox5.Text & vbLf & _
TextBox6.Text

If TextBox4.Text <> "" And TextBox5.Text <> "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text & vbLf & _
TextBox5.Text & vbLf & _
TextBox6.Text
============================================================================
========

 

[Bob Phillips]

Hi Cameron,

You don't need all of those tests, they will append the value to the
activecell multiple times. All you need is

'TextBox3.Text has to have some value before getting to this point, so
include it
' in the ActiveCell.Value now.
With ActiveCell
.Value = TextBox3.Text
'Check if the remaining TextBoxes have any value and collate.
If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox4.Text
If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox5.Text
If TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
.Value = .Value & vbLf & TextBox6.Text
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

This should be a little better

Dim strVal As Strin

strVal = TextBox3.Tex
If TextBox4.Text <> "" Then strVal = strVal & vbLf & TextBox4.Tex
If TextBox5.Text <> "" Then strVal = strVal & vbLf & TextBox5.Tex
If TextBox6.Text <> "" Then strVal = strVal & vbLf & TextBox6.Tex

ActiveCell.Value = strVa

-Brad Vontur

 

[Cameron]

Bob, many thanks for your suggestion.

Hi Cameron,

You don't need all of those tests, they will append the value to the
activecell multiple times. All you need is

'TextBox3.Text has to have some value before getting to this point, so
include it
' in the ActiveCell.Value now.
With ActiveCell
.Value = TextBox3.Text
'Check if the remaining TextBoxes have any value and collate.
If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox4.Text
If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox5.Text
If TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
.Value = .Value & vbLf & TextBox6.Text
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


============================================================================
============================================================================

 

[Cameron]

Brad,
A little better - I should have seen that myself.
Thank-you for highlighting my own oversight.
Cheers,
Cameron

 

[John Williams]

Hi all,
Have the following code(portion of) and would like to hear of any
suggestions to making it more efficient.

Each textbox is appended to the active cell only if it isn't blank, so
this can be simplified by considering each textbox in turn:

Dim text As String

ActiveCell.Value = textbox3.text
text = ActiveCell.Value
If textbox4.text <> "" Then text = text & vbLf & textbox4.text
If textbox5.text <> "" Then text = text & vbLf & textbox5.text
If textbox6.text <> "" Then text = text & vbLf & textbox6.text
ActiveCell.Value = text