PDA

View Full Version : InputBox help



aztariq
11-10-2013, 08:36 PM
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
11-11-2013, 01:06 AM
...lets try again...

GTO
11-11-2013, 01:10 AM
...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
11-11-2013, 09:38 AM
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
11-11-2013, 02:22 PM
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
11-11-2013, 08:20 PM
Thank you guys so much