InputBox entries & advice

[Howard]

What is the proper Type if the input data in an input box is as follows, is this a string or an array? The individual characters will later be used as numbers in a If c.value = OneOfTheseNumbers Then

Entries could be:

1,9
1,2,3,11,6,13
6

Type:=2 Text (a string)
Type:=64 An array of values

And how can I take this "set" of entries from the InputBox and list them in a column as individual numbers. (That range will be Dim IndexLibary As Range("XX:YY")in the code)

Thanks,
Howard

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 08:44:14 -0700 (PDT) schrieb Howard:

What is the proper Type if the input data in an input box is as follows, is this a string or an array? The individual characters will later be used as numbers in a If c.value = OneOfTheseNumbers Then

Entries could be:

1,9
1,2,3,11,6,13
6

it is Type:=3 => number (1) + text(2)

And how can I take this "set" of entries from the InputBox and list them in a column as individual numbers. (That range will be Dim IndexLibary As Range("XX:YY")in the code)

try:

Sub Test()
Dim strIn As String
Dim varOut As Variant
Dim myCount As Integer

strIn = Application.InputBox("Enter one number or " _
& "more numbers comma delimited", Type:=3)
myCount = Len(strIn) - _
Len(WorksheetFunction.Substitute(strIn, ",", ""))
If myCount = 0 Then
[A1] = strIn
Else
varOut = Split(strIn, ",")
Cells(1, 1).Resize(myCount + 1, 1) = _
WorksheetFunction.Transpose(varOut)
End If
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 18:03:23 +0200 schrieb Claus Busch:

it is Type:=3 => number (1) + text(2)

you also can do it with an array (Type:=64) but then you have to enter
more than one number as array with curled brackets ({1,3,5,7})
I did it here with comma but I don't know your delimiter for arrays.
A single number don't need the brackets.

Sub Test2()
Dim varOut As Variant

varOut = Application.InputBox("Enter a single number or " _
& "an array of numbers", Type:=64)

If UBound(varOut) = 1 Then
[A1] = varOut
Else
Cells(1, 1).Resize(UBound(varOut), 1) = _
WorksheetFunction.Transpose(varOut)
End If
End Sub


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Sun, 14 Jul 2013 18:03:23 +0200 schrieb Claus Busch:

it is Type:=3 => number (1) + text(2)

you also can do it with an array (Type:=64) but then you have to enter

more than one number as array with curled brackets ({1,3,5,7})

I did it here with comma but I don't know your delimiter for arrays.

A single number don't need the brackets.



Sub Test2()

Dim varOut As Variant



varOut = Application.InputBox("Enter a single number or " _

& "an array of numbers", Type:=64)



If UBound(varOut) = 1 Then

[A1] = varOut

Else

Cells(1, 1).Resize(UBound(varOut), 1) = _

WorksheetFunction.Transpose(varOut)

End If

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

The first suggested code looks like it is best suite for where I'm going with this 'lil project.

Works good. I can read it for the most part but danged if I can write like that.

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 09:32:15 -0700 (PDT) schrieb Howard:

The first suggested code looks like it is best suite for where I'm going with this 'lil project.

glad to help and that the code works for you


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Sun, 14 Jul 2013 09:32:15 -0700 (PDT) schrieb Howard:






glad to help and that the code works for you





Regards

Claus B.

Hi Claus,

Indeed it works fine until it gets to the msgbox and there I have messed something up, can't seem to get c to behave for me.

Just want to take each value in IndexLibry and if a match is in IndexCol then do the MyStringVariable thing to the text box.

Howard

Option Explicit

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

Dim strIn As String
Dim varOut As Variant
Dim myCount As Integer

Set IndexCol = Range("A2:A12")
Set IndexLibry = Range("A15:A24")

strIn = Application.InputBox("Enter one number or " _
& "more numbers comma delimited", Type:=3)
myCount = Len(strIn) - _
Len(WorksheetFunction.Substitute(strIn, ",", ""))
IndexLibry.ClearContents

If myCount = 0 Then
[A15] = strIn
Else
varOut = Split(strIn, ",")
Cells(15, 1).Resize(myCount + 1, 1) = _
WorksheetFunction.Transpose(varOut)
End If

For Each c In IndexLibry

MsgBox "After here = Type Mismatch"

If c.Value = IndexCol.Value Then
MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _
& ", " & c.Offset(0, 15) & ", " & c.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 11:26:45 -0700 (PDT) schrieb Howard:

For Each c In IndexLibry

MsgBox "After here = Type Mismatch"

If c.Value = IndexCol.Value Then

you set a single cells value equal to a ranges value.

try:

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


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Sun, 14 Jul 2013 11:26:45 -0700 (PDT) schrieb Howard:






you set a single cells value equal to a ranges value.



try:



For Each c In IndexLibry

Set rngC = IndexCol.Find(c, LookIn:=xlValues)

If Not rngC Is Nothing Then

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

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

& ", " & rngC.Offset(0, 15) & ", " & rngC.Offset(0, 18)

ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _

& vbCr & MyStringVariable

End If

Next

Regards

Claus B.

Hi Claus,

Would you mind taking a look at my worksheet, it seems it cannot distinguish between 1 and 10 and returns commas for each empty cell in IndexLibry.

A good test would be to 1 through 10 and note the returns in the text box.
Enter just a 1 and note
Enter just a 10 and note
Others seem to be okay, haven't tested 2 and 20, 3 and 30 etc.

https://www.dropbox.com/s/cgriavphlf8z02y/Active X Text Box List Code Example.xlsm

Howard

 

[Howard]

Hi Claus,



Would you mind taking a look at my worksheet, it seems it cannot distinguish between 1 and 10 and returns commas for each empty cell in IndexLibry.



A good test would be to 1 through 10 and note the returns in the text box.

Enter just a 1 and note

Enter just a 10 and note

Others seem to be okay, haven't tested 2 and 20, 3 and 30 etc.



https://www.dropbox.com/s/cgriavphlf8z02y/Active X Text Box List Code Example.xlsm



Howard

Wrong link, I'm all screwed up here!

Will try again for correct link.

Howard

 

[Howard]

I believe this to be the link


https://www.dropbox.com/s/rjcbl1cljvz37bc/Book2 Active X Multiple Index No. Drop Box.xlsm

Howard

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 12:40:07 -0700 (PDT) schrieb Howard:

Wrong link, I'm all screwed up here!

the link goes to "Active X Text Box List Code Example.xlsm"
There you had a question about two number 4,9
Try:

Sub TheT_Box_Claus()
Dim MyStringVariable As String
Dim IndxNum As Variant
Dim varOut As Variant
Dim IndexCol As Range
Dim i As Integer
Dim c As Range

Set IndexCol = Range("A2:A7")

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

varOut = Split(IndxNum, ",")
For Each c In IndexCol
For i = LBound(varOut) To UBound(varOut)
If c = CInt(varOut(i)) Then
MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _
& ", " & c.Offset(0, 15) & ", " & c.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If
Next i
Next c
End Sub

Please put your code in a standard module and not in the sheets module.
Only with AxtiveX-Controls the code must be in teh code module of the
sheet where the controls are in.


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 12:45:35 -0700 (PDT) schrieb Howard:

https://www.dropbox.com/s/rjcbl1cljvz37bc/Book2 Active X Multiple Index No. Drop Box.xlsm

you have to lookat xlwhole and only in cells with values.
Try the loop:

For Each c In IndexLibry
Set rngC = IndexCol.Find(c, LookIn:=xlValues, lookat:=xlWhole)
If Not rngC Is Nothing And rngC <> "" Then
MyStringVariable = rngC.Offset(0, 4) & ", " & rngC.Offset(0, 1) _
& ", " & rngC.Offset(0, 2) & ", " & rngC.Offset(0, 14) _
& ", " & rngC.Offset(0, 15) & ", " & rngC.Offset(0, 18)

ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If
Next


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Sun, 14 Jul 2013 12:45:35 -0700 (PDT) schrieb Howard:






you have to lookat xlwhole and only in cells with values.

Try the loop:



For Each c In IndexLibry

Set rngC = IndexCol.Find(c, LookIn:=xlValues, lookat:=xlWhole)

If Not rngC Is Nothing And rngC <> "" Then

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

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

& ", " & rngC.Offset(0, 15) & ", " & rngC.Offset(0, 18)



ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _

& vbCr & MyStringVariable

End If

Next





Regards

Claus B.

A work of art!!
Thanks so much.

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Sun, 14 Jul 2013 13:26:54 -0700 (PDT) schrieb Howard:

Thanks so much.

please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the folder "Howard".


Regards
Claus B.

 

[GS]

Not meaning to take away from Claus' suggestion, but here's how I'd
handle the input so it's easier to maintain...

Sub ListNums()
Dim vNums
vNums = Application.InputBox("Enter a number" _
& vbCrLf & "OR" & vbCrLf _
& "a list of numbers separated by commas", Type:=3)
vNums = Split(vNums, ",")
If UBound(vNums) = 0 Then
[A1] = vNums
Else
[A1].Resize(UBound(vNums) + 1, 1) _
= WorksheetFunction.Transpose(vNums)
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Howard]

Not meaning to take away from Claus' suggestion, but here's how I'd

handle the input so it's easier to maintain...



Sub ListNums()

Dim vNums

vNums = Application.InputBox("Enter a number" _

& vbCrLf & "OR" & vbCrLf _

& "a list of numbers separated by commas", Type:=3)

vNums = Split(vNums, ",")

If UBound(vNums) = 0 Then

[A1] = vNums

Else

[A1].Resize(UBound(vNums) + 1, 1) _

= WorksheetFunction.Transpose(vNums)

End If

End Sub

Garry

Hi Garry,

Thanks for the input on the input (pun intended)

I copied your and Claus' codes and posted the in a sheet module to better compare the scripts.

Of course you and Claus could have a meaningful discussion on the codes between yourselves, but the finer points, sadly, would be lost to me.

I can take the codes offered and know what they will do because I ask "How do you...?" and get amazing (to me) answers, plug them in and away I go on to the next hurdle.

I have picked up quite a bit from you and Claus and others, and it stokes my keeps-me-off-the-streets Excel hobby.

Always appreciate the help.

Regards,
Howard

 

[GS]

Yes, Claus provides some pretty awesome solutions. Glad I'm able to
contribute as well! (Don't want you becoming a 'street person' now do
we?)<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion