How do I assign inputbox input to variable for each vba excel

Question

I have a for statement in a VBA script that goes through each cell in a range (number of cells in range is variable based on user input - could be three cells could be 100). Each instance of the for loop calls an input box. How do I assign the user input from each instance of the for loop to a variable for later use? Here is the for with the input box:

For Each cell In MyQCData
text_string = cell.Value
WrdArray() = split(text_string, ",")
    For i = LBound(WrdArray) To UBound(WrdArray)
        strg = strg & vbNewLine & "Part No. " & i & " - " & WrdArray(i)
    Next i
        InputBox ("This part requires a " & WrdArray(0) & " measurement of the " & _  
        WrdArray(1) & vbNewLine & vbNewLine _
        & "The range for this is input is " & vbNewLine & vbNewLine & "Lower Control Limit     " _
        & WrdArray(2) & vbNewLine & "Upper Control Limit     " & WrdArray(3))
        Erase WrdArray()
Next cell

YowE3K · Accepted Answer · 2017-05-19 23:00:40Z

1

Yes, use an array:

Dim inputBoxAnswers() As String
ReDim inputBoxAnswers(1 To MyQCData.Cells.Count)
Dim cellCounter As Long
For Each cell In MyQCData
    text_string = cell.Value
    WrdArray() = split(text_string, ",")

    'Is this loop needed???
    For i = LBound(WrdArray) To UBound(WrdArray)
        strg = strg & vbNewLine & "Part No. " & i & " - " & WrdArray(i)
    Next i

    cellCounter = cellCounter + 1
    inputBoxAnswers(cellCounter) = InputBox("This part requires a " & _
                                   WrdArray(0) & " measurement of the " & _  
                                   WrdArray(1) & _
                                   vbNewLine & vbNewLine & _
                                   "The range for this is input is " & _
                                   vbNewLine & vbNewLine & _
                                   "Lower Control Limit     " & WrdArray(2) & _
                                   vbNewLine & _
                                   "Upper Control Limit     " & WrdArray(3))
Next cell

If your MyQCData range is not a single column or a single row, you may find it easier to use a two-dimensional array, which could (perhaps) be dimensioned using

Dim inputBoxAnswers() As String
ReDim inputBoxAnswers(1 To MyQCData.Rows.Count, 1 To MyQCData.Columns.Count)

but then you will need to rework the indexes to use when assigning the elements their values. It would probably need to be

inputBoxAnswers(cell.Row - MyQCData.Row + 1, cell.Column - MyQCData.Column + 1) = ....

but a lot depends on how you intend to use the array afterwards.

answered May 19, 2017 at 23:00

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BruceWayne Over a year ago

I like it. Instead of using a new variable, cellCounter, could you maybe do MyQCData.cell.row? Or something, the idea being the main range has cells, and use each cells' row within the main range?

YowE3K Over a year ago

@BruceWayne - If MyQCData was one-dimensional, you could use inputBoxAnswers(cell.Row - MyQCData.Row + 1), or you could dimension using ReDim inputBoxAnswers(MyQCData.Row To MyQCData.Row + MyQCData.Rows.Count - 1) and then use inputBoxAnswers(cell.Row). (Obviously, if MyQCData started on row 1, those expressions simplify a lot because MyQCData.Row - 1 will be 0.) So much depends on the shape of MyQCData and the intended use of the answers that it is hard to guess what is the best way.

BruceWayne · Accepted Answer · 2017-05-20 01:00:24Z

1

It's kind of hard to tell without the rest of your code, but I assume that MyQCData is a Range. Try the below. I sort of "brute forced" the Inputbox look with k, FYI.

Dim k As Long
k = 0
Dim inputArr() As Variant
ReDim inputArr(myqcdData.Cells.Count)
For Each cell In MyQCData
    text_string = cell.Value

    WrdArray() = Split(text_string, ",")
    For i = LBound(WrdArray) To UBound(WrdArray)
        strg = strg & vbNewLine & "Part No. " & i & " - " & WrdArray(i)
    Next i
    inputArr(k) = InputBox("This part requires a " & WrdArray(0) & " measurement of the " & _
                           WrdArray(1) & vbNewLine & vbNewLine _
                           & "The range for this is input is " & vbNewLine & vbNewLine & "Lower Control Limit     " _
                           & WrdArray(2) & vbNewLine & "Upper Control Limit     " & WrdArray(3))
    k = k + 1
    Erase WrdArray()
Next cell

'Check each value in the array. This is optional and can be removed/commented out
For k = LBound(inputArr) To UBound(inputArr)
    Debug.Print inputArr(k)
Next k

Edited per @Yow's astute comment

edited May 20, 2017 at 1:00

answered May 19, 2017 at 22:55

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

2 Comments

YowE3K Over a year ago

Tsk, tsk, tsk - Erase WrdArray() inside a loop that is using WrdArray? (As far as I can see, the OP doesn't need the Erase anyway.) And do the Dim inputArr() As Variant ReDim inpurArr(myqcdData.Cells.Count) outside the For Each Cell loop or you will wipe everything every time you go to the next Cell. What time is it on Mars - you must be getting tired! :D

BruceWayne Over a year ago

@YowE3K truth time - it was close to Home Time at Wayne Enterprises when I worked on that and may have let one or two things slip my eye. I'll edit when I get home from savi...I mean business dinner.

David · Accepted Answer · 2017-05-19 22:59:11Z

0

I'm going to dress this in a standalone code that you can easily run to get a feel for what's going on. Array variables must be declared hence Dim userInput(99) and the 99 is the upper limit (0-99 = 100 possible values). The first line of the first loop sets the variables, that's userInput(j) = InputBox("Sample InputBox", "InputBox Title", "blah" & j) the "blah" & j bit is the default entry, which is useful when you're writing/debugging as it's a lot faster to keep entering dummy data...

Sub inputBoxEg()
    Dim userInput(99)
    Dim MyQCData As Range

    Set MyQCData = Range("A1:A4")

    'Set InputBox inputs to a variable array called userInput:
    j = 0
    For Each cell In MyQCData
        userInput(j) = InputBox("Sample InputBox", "InputBox Title", "blah" & j)
        If userInput(j) = "" Then Exit Sub 'if user pressed cancel or entered blank
        j = j + 1
    Next cell

    'Collate variables collected by InputBoxes in a text string called allInputs:
    allInputs = ""
    For i = 0 To j - 1
        If i = 0 Then
            allInputs = i & ": " & userInput(i)
        Else
            allInputs = allInputs & vbNewLine & i & ": " & userInput(i)
        End If
    Next i

    MsgBox allInputs
End Sub

answered May 19, 2017 at 22:59

David

1,2321 gold badge10 silver badges19 bronze badges

2 Comments

TheWhackHack Over a year ago

I haven't had a chance to play with the suggestions here yet, but I can't thank you enough for the responses. From reading through i see enough that I should be able to get this to go. I love this site, have learned so much - maybe i'll be answering questions of others some day. AWESOME!

TheWhackHack Over a year ago

Just had an opportunity to play with the suggestions. PERFECT, THANK YOU!

Collectives™ on Stack Overflow

How do I assign inputbox input to variable for each vba excel

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related