Input box with multiple choices

  • Thread starter Thread starter jsd219
  • Start date Start date
J

jsd219

Is there a way to make an input box first ask how many words to look
for, once you have put in a number (the code should probably limit the
number to no more than 10) a new input box shows up asking for each
word.

Example

First input box: "How many words"
' my answer is 3

Next input box "enter first word"
'after i hit enter

Next input box "enter second word"
'after i hit enter

next input box "enter third word"

God bless
jsd219
 
Hi jsd219,

The following will collect what you're looking for as I understand you...

*******************************************
Sub Inputs()

Dim WordNum, Word

WordNum = InputBox("Enter Number of Words", "Specify Number")

ReDim Word(WordNum)

For n = 1 To WordNum
StrMsg = "Enter Word " & n
Word(n) = InputBox(StrMsg, "Enter Word " & n)
Next n

End Sub
*******************************************

So Word(1) will be the first, Word(2) will be the 2nd and so on for each
instance.
If you want to limit the number available, just add something like:
If WordNum>10 Then
' advise of error and either end or use a GoTo to return to start
End If

HTH
DS
 
How would i incorporate this in to my code?

my code needs to now look for any of the words created from your script
and execute accordingly. if there are multiple words found it needs to
place them in Column V with a cariage return after each word

example: below is my current code

Dim rng As Range
Dim cell As Range
Dim first As Long
Dim second As Long

myword = InputBox("Enter word to find ")

If Trim(myword) = "" Then
MsgBox "quitting!"
Exit Sub
End If

'This is to find myword2
'myword2 = InputBox("Enter word to find ")
'
' If Trim(myword2) = "" Then
' MsgBox "quitting!"
' Exit Sub
' End If

With Worksheets("Resources")
Set rng = .Range("N1", .Cells(.Rows.Count, "N").End(xlUp))
End With

For Each cell In rng
first = InStr(1, cell.Value, myword, vbTextCompare)
' This is used to get myword2's input
' second = InStr(1, cell.Value, myword2, vbBinaryCompare)
If first Or second Then
Cells(cell.Row, Range("V:V").Column).Value = myword
' This line below will join myword and myword2 together with a
cariage return
' Cells(cell.Row, Range("V:V").Column).Value = myword & vbLf &
myword2
End If

Next

God bless
jsd219
 
Hi DS, i am trying to figure out where the script stores the words. in
other words, lets say i choose 3 words and then input them. what is the
call for those three words. I tried WordNum but it does not work, i
also tried "n" and it did not work. any ideas?

God bless
jsd219
 
Howdy jsd219,

okies, here's how it works.

When you define "WORDNUM", you're telling VB how many words will be entered.
You then define WORD to occur as many times as WORDNUM, in the format:
1st Word = "WORD(1)"
2nd Word = "WORD(2)"
3rd Word = "WORD(3)"

and so on. "n" was set to the same value as "WORDNUM", so "n" occurs as many
times as the originally-defined "WORDNUM", so when we say "WORD(n)" to return
a particular word, replace "(n)" with the instance of "WORD" you're looking
for. If you're in a loop where we have a "For n = 1 to WordNum", then action
"Word(n)" rather than a specific instance, so that every instance of Word is
affected by the code you're calling.


As for incorporating into your own code posted above, if you replace the line:

myword = InputBox("Enter word to find")

with the code I posted earlier, this will allow the user to define the words
required.

Then replace your search criteria with a For... Next Loop to look for the
values defined above, e.g.

For n = 1 to WordNum 'sets the number of times to run
' Search for "Word(n)" and take appropriate action if/when found
Next n 'moves to the next instance of n, ie the next word to run

I'm not sure from your code posted how you're looking for these words / what
you're doing when found, as it appears to be all commented, but I'm sure
you'll be able to get it from here.

HTH
DS
 
Ok, forgive me please i am very knew to this and if you don't mind if
would like to show you what i have and see where i am going wrong:

What i am trying to make it do is, depending on how many words i enter
in the input box, look for those words either individually in cells or
combined in one cell, once found it places the word or words in Column
"V" of its same row.

So, if i put in 2 words and make those words Word(1) "Jack", Word(2)
"Jill" it will go through column "N" and everytime it finds cells with
Jack it will place Jack in Column "V" of the same row it found it in,
it will do the same for Jill and if it finds cells with Jack and Jill
together it will take both of them and place them into Column "V" of
the same row with cariage returns seperating them.

Dim rng As Range
Dim cell As Range

Dim WordNum, Word

WordNum = InputBox("Enter Number of Words", "Specify Number")

If Trim(WordNum) = "" Then
MsgBox "quitting!"
Exit Sub
End If

ReDim Word(WordNum)

For n = 1 To WordNum
StrMsg = "Enter Word " & n
Word(n) = InputBox(StrMsg, "Enter Word " & n)
Next n

With Worksheets("Resources")
Set rng = .Range("N1", .Cells(.Rows.Count, "N").End(xlUp))
End With

For n = 1 To WordNum
Cells(cell.Row, Range("V:V").Column).Value = Word(n)
' vbLf is cariage return

Next n

God bless
jsd219
 
Ok, forgive me please i am very knew to this and if you don't mind if
would like to show you what i have and see where i am going wrong:

What i am trying to make it do is, depending on how many words i enter
in the input box, look for those words either individually in cells or
combined in one cell, once found it places the word or words in Column
"V" of its same row.

So, if i put in 2 words and make those words Word(1) "Jack", Word(2)
"Jill" it will go through column "N" and everytime it finds cells with
Jack it will place Jack in Column "V" of the same row it found it in,
it will do the same for Jill and if it finds cells with Jack and Jill
together it will take both of them and place them into Column "V" of
the same row with cariage returns seperating them.

Dim rng As Range
Dim cell As Range

Dim WordNum, Word

WordNum = InputBox("Enter Number of Words", "Specify Number")

If Trim(WordNum) = "" Then
MsgBox "quitting!"
Exit Sub
End If

ReDim Word(WordNum)

For n = 1 To WordNum
StrMsg = "Enter Word " & n
Word(n) = InputBox(StrMsg, "Enter Word " & n)
Next n

With Worksheets("Resources")
Set rng = .Range("N1", .Cells(.Rows.Count, "N").End(xlUp))
End With

For n = 1 To WordNum
Cells(cell.Row, Range("V:V").Column).Value = Word(n)
' vbLf is cariage return

Next n


I really appreciate all of your help and Thank you for your time

God bless
jsd219
 
Back
Top