I believe the problem is with the InputBox IndxNum and the Variant c.

The IndxNum in this case is a number and the For Each c requires a Variant.
Seems they not be compatible.

Also, have not got far enough to test it, but will the next MyStringVariable be listed below the previous or do I need the & vbCr at the end of MyStringVariable?

I have Multi Line = TRUE in properties.

Thanks,
Howard

Option Explicit

Sub TheT_Box()
Dim MyStringVariable As String
Dim IndxNum As Long
Dim IndexCol As Range
Dim c As Variant

Set IndexCol = Range("A2:A7")

IndxNum = Application.InputBox(Prompt:="Enter an Number.", _
Title:="Enter Index Number", Type:=1) ' 1 = number
If IndxNum = "" Or IndxNum = "False" Then Exit Sub

For Each c In Range("IndexCol") 'Range("A2:A7")
If c.Value = IndxNum Then
MyStringVariable = c.Offset(0, 4).Value & ", " & c.Offset(0, 1).Value _
& c.Offset(0, 2).Value & ", " & c.Offset(0, 14).Value _
& ", " & c.Offset(0, 15).Value & ", " & c.Offset(0, 18).Value '& vbCr
TextBox1.Text = MyStringVariable
End If
Next ' c

End Sub

Hi Howard,

Am Sat, 13 Jul 2013 13:10:27 -0700 (PDT) schrieb Howard:

I believe the problem is with the InputBox IndxNum and the Variant c.

The IndxNum in this case is a number and the For Each c requires a Variant.
Seems they not be compatible.

Also, have not got far enough to test it, but will the next MyStringVariable be listed below the previous or do I need the & vbCr at the end of MyStringVariable?

c has to be a range. You don't need a vbCr at the end of the string.
Try:

Sub TheT_Box()
Dim MyStringVariable As String
Dim IndxNum As Long
Dim IndexCol As Range
Dim c As Range

Set IndexCol = Range("A2:A7")

IndxNum = Application.InputBox(Prompt:="Enter an Number.", _
Title:="Enter Index Number", Type:=1) ' 1 = number
If IndxNum = False Then Exit Sub

For Each c In IndexCol 'Range("A2:A7")
If c.Value = IndxNum Then
MyStringVariable = c.Offset(0, 4).Value & ", " & c.Offset(0,
1).Value _
& c.Offset(0, 2).Value & ", " & c.Offset(0, 14).Value _
& ", " & c.Offset(0, 15).Value & ", " & c.Offset(0, 18).Value '&
vbCr
ActiveSheet.TextBox1.Text = MyStringVariable
End If
Next ' c

End Sub

You can test IndxNum also against greater MAX(IndexCol) and smaller
MIN(IndexCol)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Sat, 13 Jul 2013 22:32:18 +0200 schrieb Claus Busch:

MyStringVariable = c.Offset(0, 4).Value & ", " & c.Offset(0,
1).Value _
& c.Offset(0, 2).Value & ", " & c.Offset(0, 14).Value _
& ", " & c.Offset(0, 15).Value & ", " & c.Offset(0, 18).Value '&
vbCr

you don't need .value because value is the standard property of a range.
So the code is better readable. And your 6 cell values are too long for
a textbox. Make a vbCr after the 4. entry (you also forgot one comma):

MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _
& vbCr & c.Offset(0, 15) & ", " & c.Offset(0, 18)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Saturday, July 13, 2013 1:38:42 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 13 Jul 2013 22:32:18 +0200 schrieb Claus Busch:



MyStringVariable = c.Offset(0, 4).Value & ", " & c.Offset(0,

1).Value _

& c.Offset(0, 2).Value & ", " & c.Offset(0, 14).Value _

& ", " & c.Offset(0, 15).Value & ", " & c.Offset(0, 18).Value '&

vbCr



you don't need .value because value is the standard property of a range.

So the code is better readable. And your 6 cell values are too long for

a textbox. Make a vbCr after the 4. entry (you also forgot one comma):



MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _

& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _

& vbCr & c.Offset(0, 15) & ", " & c.Offset(0, 18)





Regards

Claus B.

For sure I had not got far enough to see that the comma was missing, did find that while testing your very workable adjustments to the code.

Two things Claus.

I knew before any running any successful code I was going to have anywhere from 30 to maybe 60 characters returned so I just made the text box about 8 normal columns wide and that is wider than I need for the 50 or so characters that I'm returning on a single line. I just figured I'd narrow it up later to suit the entries.

A single line for each entry in the text box is a goal which doesn't seem to be a problem. But you would not have said anything if you did not have some concern about the width of the text box. So I'm open to suggestion about this.

However, if I try to add additional strings to the text box, it overwrites the previous entry. Can't seem to figure out how to add subsequent strings below the last . Which is also a goal.

Multiple strings on a single line is what I am shooting for.

Howard

Hi Howard,

Am Sat, 13 Jul 2013 14:45:14 -0700 (PDT) schrieb Howard:

A single line for each entry in the text box is a goal which doesn't seem to be a problem. But you would not have said anything if you did not have some concern about the width of the text box. So I'm open to suggestion about this.

you can set the width as you like it and you can work with the textbox.
It is only a optical thing

However, if I try to add additional strings to the text box, it overwrites the previous entry. Can't seem to figure out how to add subsequent strings below the last . Which is also a goal.

you can hold your former entries in the textbox if you change the code
to:
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable

And that you must not loop through your range you could use Range.Find:

Set c = IndexCol.Find(IndxNum, LookIn:=xlValues)
If Not c Is Nothing Then
MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & vbCr & c.Offset(0, 14) _
& ", " & c.Offset(0, 15) & ", " & c.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If

Make sure that in this case c has to be declared as range


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Sat, 13 Jul 2013 23:58:37 +0200 schrieb Claus Busch:

you can set the width as you like it and you can work with the textbox.
It is only a optical thing

you also can set the property "AutoSize" to true and the textbox will
adjust to the text.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch wrote:

you don't need .value because value is the standard property of a range.

correct. But I also write .value explicitely when I mean it.
In my opinin, this makes the code better readable than leaving it out.

Volker
--
Im übrigen bin ich der Meinung, dass TCPA/TCG verhindert werden muss

Wenn es vom Himmel Zitronen regnet, dann lerne, wie man Limonade macht