locked
InputBox prompt that changes while looping RRS feed

  • Question

  • I am attempting to write a macro using the InputBox that asks users to input a number of values.

    I would like the input box to loop through as many times are required while changing the prompt to the current iteration of the loop. Is this within the bounds of the InputBox functionality? is there a better/easier way to do it?

    This is what I have attempted:

      Range("B6").Value = InputBox("How many things are there?", "How many things?")

      Dim iCntr As Byte
      Dim iStng As String
      
       j = Range("B6").Value

      For iCntr = 1 To j
      iStng = "Please enter the value for thing: " & iCntr
      Range("D(iCntr)").Value = Application.InputBox(Prompt:=iStng, Title:="Payoffs", Type:=1)
      Next iCntr

    Hopefully this gives you an idea of what I am trying to achieve. Help and ideas would be greatly appreciated.


    Saturday, September 6, 2014 11:34 PM

Answers

  • You method appears to be OK but I have edited the code with correct syntax for VBA. See comments.

    Sub test()

        Dim iCntr As Long   'Edited by OssieMac
        Dim iStng As String
        Dim j As Long
        Dim varInput As Variant
       
        Range("B6").Value = InputBox("How many things are there?", "How many things?")
       
        j = Range("B6").Value
       
        For iCntr = 1 To j
            iStng = "Please enter the value for thing: " & iCntr
            'Following range address edited by OssieMac
            Range("D" & iCntr).Value = Application.InputBox(Prompt:=iStng, Title:="Payoffs", Type:=1)
        Next iCntr

    End Sub


    Regards, OssieMac

    • Marked as answer by Name_As_String Wednesday, September 17, 2014 2:18 PM
    Sunday, September 7, 2014 3:39 AM

All replies

  • You method appears to be OK but I have edited the code with correct syntax for VBA. See comments.

    Sub test()

        Dim iCntr As Long   'Edited by OssieMac
        Dim iStng As String
        Dim j As Long
        Dim varInput As Variant
       
        Range("B6").Value = InputBox("How many things are there?", "How many things?")
       
        j = Range("B6").Value
       
        For iCntr = 1 To j
            iStng = "Please enter the value for thing: " & iCntr
            'Following range address edited by OssieMac
            Range("D" & iCntr).Value = Application.InputBox(Prompt:=iStng, Title:="Payoffs", Type:=1)
        Next iCntr

    End Sub


    Regards, OssieMac

    • Marked as answer by Name_As_String Wednesday, September 17, 2014 2:18 PM
    Sunday, September 7, 2014 3:39 AM
  • The following is another option that loops infinitely until the user Cancels in the InputBox dialog. Note that cannot test for False to detect when the user cancels because if the user enters zero then zero also equates to False and will exit the loop. Testing for the variable type is reliable because it will only be a Boolean variable if user cancels and variable returns false.

    Sub test2()
        'This sub loops until User Cancels in the InputBox dialog
        'Testing for TypeName in lieu of testing for False
        'is required because entering zero also equates to False.
        Dim iCntr As Long
        Dim iStng As String
        Dim varInput As Variant 'Use Variant so can accept Boolean False on Cancel
       
        iCntr = 1
        Do
            iStng = "Please enter the value for thing: " & iCntr
            varInput = Application.InputBox(Prompt:=iStng, Title:="Payoffs", Type:=1)
            If TypeName(varInput) = "Boolean" Then Exit Do    'When user Cancels
            Range("D" & iCntr).Value = varInput
            iCntr = iCntr + 1
        Loop

    End Sub


    Regards, OssieMac

    Sunday, September 7, 2014 3:52 AM
  • Much thanks.
    Sunday, September 7, 2014 4:54 AM