InputBox help

[aztariq]

Hi all. I am having trouble with a function I'm trying to write. The user has a sheet in the workbook that has an ActiveX button on it which when clicked opens InputBoxes where the user enters values which are then put into an array.

The catch is....

If they enter nothing or press enter, it needs to continue to the next InputBox
If they press Cancel then it needs to exit the whole subroutine altogether

NOTE: YES, I know I can have a loop go through the array (greatly reducing the amount of written code) and maybe use recursion to continually call InputBox and stick the values in but I've been away from vba and all programming for a while so I just coded each iteration line by line. Even with the loop I still have the same problem of differentiating between nothing being entered and the cancel button being hit.

Here is my code:

Sub Slope()

Dim Slopes() As Double
ReDim Slopes(11, 1)

Slopes(0, 0) = Application.InputBox("1st Month Put Slope", "Slopes")
If Slopes(0, 0) = False Then Exit Sub
Slopes(0, 1) = Application.InputBox("1st Month Call Slope", "Slopes")
If Slopes(0, 1) = False Then Exit Sub
Slopes(1, 0) = Application.InputBox("2nd Month Put Slope", "Slopes")
If Slopes(1, 0) = False Then Exit Sub
Slopes(1, 1) = Application.InputBox("2nd Month Call Slope", "Slopes")
If Slopes(1, 1) = False Then Exit Sub
Slopes(2, 0) = Application.InputBox("3rd Month Put Slope", "Slopes")
If Slopes(2, 0) = False Then Exit Sub
Slopes(2, 1) = Application.InputBox("3rd Month Call Slope", "Slopes")
If Slopes(2, 1) = False Then Exit Sub
Slopes(3, 0) = Application.InputBox("4th Month Put Slope", "Slopes")
If Slopes(3, 0) = False Then Exit Sub
Slopes(3, 1) = Application.InputBox("4th Month Call Slope", "Slopes")
If Slopes(3, 1) = False Then Exit Sub
Slopes(4, 0) = Application.InputBox("5th Month Put Slope", "Slopes")
If Slopes(4, 0) = False Then Exit Sub
Slopes(4, 1) = Application.InputBox("5th Month Call Slope", "Slopes")
If Slopes(4, 1) = False Then Exit Sub
Slopes(5, 0) = Application.InputBox("6th Month Put Slope", "Slopes")
If Slopes(5, 0) = False Then Exit Sub
Slopes(5, 1) = Application.InputBox("6th Month Call Slope", "Slopes")
If Slopes(5, 1) = False Then Exit Sub
Slopes(6, 0) = Application.InputBox("7th Month Put Slope", "Slopes")
If Slopes(6, 0) = False Then Exit Sub
Slopes(6, 1) = Application.InputBox("7th Month Call Slope", "Slopes")
If Slopes(6, 1) = False Then Exit Sub
Slopes(7, 0) = Application.InputBox("8th Month Put Slope", "Slopes")
If Slopes(7, 0) = False Then Exit Sub
Slopes(7, 1) = Application.InputBox("8th Month Call Slope", "Slopes")
If Slopes(7, 1) = False Then Exit Sub
Slopes(8, 0) = Application.InputBox("9th Month Put Slope", "Slopes")
If Slopes(8, 0) = False Then Exit Sub
Slopes(8, 1) = Application.InputBox("9th Month Call Slope", "Slopes")
If Slopes(8, 1) = False Then Exit Sub
Slopes(9, 0) = Application.InputBox("10th Month Put Slope", "Slopes")
If Slopes(9, 0) = False Then Exit Sub
Slopes(9, 1) = Application.InputBox("10th Month Call Slope", "Slopes")
If Slopes(9, 1) = False Then Exit Sub
Slopes(10, 0) = Applicaion.InputBox("11th Month Put Slope", "Slopes")
If Slopes(10, 0) = False Then Exit Sub
Slopes(10, 1) = Application.InputBox("11th Month Call Slope", "Slopes")
If Slopes(10, 1) = False Then Exit Sub
Slopes(11, 0) = Application.InputBox("12th Month Put Slope", "Slopes")
If Slopes(11, 0) = False Then Exit Sub
Slopes(11, 1) = Application.InputBox("12th Month Call Slope", "Slopes")
If Slopes(11, 1) = False Then Exit Sub

' MsgBox Slopes(0, 0)
' MsgBox Slopes(0, 1)

End Sub

[GTO]

...lets try again...

[GTO]

...The catch is....

If they enter nothing or press enter, it needs to continue to the next InputBox
If they press Cancel then it needs to exit the whole subroutine altogether

NOTE: YES, I know I can have a loop go through the array (greatly reducing the amount of written code) and maybe use recursion to continually call InputBox and stick the values in but I've been away from vba and all programming for a while so I just coded each iteration line by line. Even with the loop I still have the same problem of differentiating between nothing being entered and the cancel button being hit.

Greetings aztariq,

Here is a loop. Note that I changed the Application.InputBox's Type to 1 (requires a number entered). Although this eliminates the user from being able to simply press the OK button, they can still enter a zero if wanted.

Sub Slope()
Dim Slopes(0 To 11, 0 To 1) As Double
Dim vntValue                As Variant
Dim y                       As Long
Dim x                       As Long
  
  For y = 0 To 11
    For x = 0 To 1
      
      vntValue = _
        Application.InputBox( _
          Switch(y = 0, "1st ", y = 1, "2nd ", y = 2, "3rd ", y = 3, "4th ", _
                 y = 4, "5th ", y = 5, "6th ", y = 6, "7th ", y = 7, "8th ", _
                 y = 8, "9th ", y = 9, "10th ", y = 10, "11th ", y = 11, "12th " _
                 ) & "Month " & IIf(x = 0, "Put ", "Call ") & "Slope", "Slopes", Type:=1)
      
      If TypeName(vntValue) = "Boolean" Then
        Exit Sub
      Else
        Slopes(y, x) = vntValue
      End If
      
    Next
  Next
  
  MsgBox Slopes(0, 0)
  MsgBox Slopes(0, 1)
  
End Sub

Hope that helps,

Mark

[aztariq]

Greetings aztariq,

Here is a loop. Note that I changed the Application.InputBox's Type to 1 (requires a number entered). Although this eliminates the user from being able to simply press the OK button, they can still enter a zero if wanted.

Here's the problem I should have clarified. The user may not want to change certain values, so to fast forward to the part he wants to change, I want them to just press Ok or hit Enter. So if a null value is given by the user, then no value is assigned to that index in the array. If the user hits cancel, then it exits altogether.

So entering zero won't work in this case because it shouldn't change it they press enter or hit ok

Thanks for your help,

Tariq

[mikerickson]

An InputBox (rather than Application.InputBox) is easier to tell when Cancel is pressed vs. entering a nullstring.
Also, if you have existing values that the user might not want to change, you can use the Default argument.

Sub test()
    Dim myArray(0 To 11, 0 To 1) As Double
    Dim uiValue As String
    Dim i As Long, j As Long
    Dim strPrompt As String
    
    myArray(0, 0) = 101: myArray(0, 1) = 102
    myArray(1, 0) = 201: myArray(1, 1) = 202
    
    For i = 0 To 11
        strPrompt = Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th", "12th")(i)
        strPrompt = strPrompt & " month "
        For j = 0 To 1

            uiValue = InputBox(strPrompt & Array("Put", "Call")(j) & " slope", Default:=myArray(i, j))
            If StrPtr(uiValue) = 0 Then Exit Sub: Rem cancel pressed

            myArray(i, j) = Val(uiValue)
        Next j
    Next i
End Sub

[aztariq]

Thank you guys so much