CODEWITHSUNDEEP
excelvba
Bradley Carrico
Bradley Carrico

Reputation: 151

Input Box Error Handling

I am having trouble handling the error associated with a input box "Cancel" click. Or in otherwords, it returns an error within the sub if the value of the input is null. I have tried looking around and still can't seem to get it quite right. Here is my attempt:

Private Sub bttnSavingsExpected_Click()
Dim expected() As String
Dim nPeriods As Integer
Dim counter As Integer
Dim savings As Single

With ActiveSheet.Range("A13")
    nPeriods = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
End With

ReDim expected(1 To nPeriods)

counter = 1

For counter = 1 To nPeriods
    expected(counter) = Range("A13").Offset(counter, 0).Value
Next

TryAgain:
On Error GoTo ErrH
counter = 1
For counter = 1 To nPeriods
    savings = InputBox("How much savings do you expect from " & expected(counter) & "?", "Savings?", Range("A13").Offset(counter, 1).Value)
    If savings = "" Then
        Exit Sub
    Else
    Range("A13").Offset(counter, 1).Value = savings
    End If
Next

Exit Sub
ErrH:
MsgBox "Please enter value. If the default value is desired then please click 'OK'.", vbOKOnly, "Do Not Click Cancel"
GoTo TryAgain

End Sub

With this attempt, the MsgBox is displayed the first click whether there is a input or not and even if I click "Ok". The second try of clicking "OK" or "Cancel" leads to being kicked back to the editor.

Upvotes: 2

Views: 6575

Answers (2)

RWB
RWB

Reputation: 127

Make sure the variable for the Inbox is set at "", then test the value for False. Much easier than anything else I have seen:

Sub WolfPackURL_input()
Dim TheURL As String
Dim SaveURL As Hyperlink
Set savedURL = Sheets("Data").Range("I1")

TheURL = ""
TheURL = Application.InputBox("Input the Sign-Up URL", "Wolfpack Weekly Players URL", "http://something", 1)

If TheURL = "False" Then
    Exit Sub
End If

ThisWorkbook.Worksheets("Data").Activate
Sheets("Data").Range("I1").Hyperlinks.Delete
Sheets("Data").Range("I1").ClearContents
Sheets("Data").Range("I1").Clear

ActiveSheet.Hyperlinks.Add anchor:=Sheets("Data").Range("I1"), Address:=TheURL, ScreenTip:="Open file", TextToDisplay:=TheURL

End Sub

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

You've got Dim savings As Single and If savings = "" Then. Thats always going to error

Try using Dim savings As Variant

Upvotes: 3

Related Questions