Dim IndxNum As Long & Dim c As Variant

H

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?

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
 
C

Claus Busch

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?
Click to expand...

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.
 
C

Claus Busch

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
Click to expand...

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.
 
H

Howard

Hi Howard,



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










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.
Click to expand...

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 8normal 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 stringsbelow the last . Which is also a goal.

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

Howard
 
C

Claus Busch

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.
Click to expand...

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.
Click to expand...

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.
 
C

Claus Busch

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
Click to expand...

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


Regards
Claus B.
 
H

Howard

Hi Howard,



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







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

adjust to the text.
Click to expand...


Well, Claus, once again you got me where I wanted to go. Everything works just fine!

I do appreciate the help.

Thanks.
Howard
 
V

Volker Neurath

Claus said:
you don't need .value because value is the standard property of a range.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Eliminate blank row at top of Text Box entries 3
Active XText box vbcr shows symbol instead of an actual carraige return 2
Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet 27
Select Case code error 8
Tried and true copy code lines fail me here 7
A simple if <> nn then Msgbox "No Match" 5
Copy row info but not in normal column sequence 6
Process dumped into an array...and then back to sheet 27

Top