CODEWITHSUNDEEP
excelvbainputbox
Roberto
Roberto

Reputation: 21

Inputbox prevent user from pressing enter when value is blank

I have the following code : Range("P" & Rows.Count).End(xlUp).offset(1).Value = InputBox("INTRODUCE ACT JUSTIFICATIV") User must enter letters in inputbox

I want to prevent the user from accidentally pressing enter if value in inputbox is blank with a personalized error message.

And pressing cancel button to exit sub.

Upvotes: 0

Views: 2129

Answers (4)

Super Symmetry
Super Symmetry

Reputation: 2875

You can use Application.InputBox which gives you a little more control. The user will be prompted until they either enter a non-blank string or press the cancel or x buttons

Dim vInput As Variant: vInput = ""
Do
    vInput = Application.InputBox("INTRODUCE ACT JUSTIFICATIV", Type:=2) '2 -> String type
    If vInput = False Then 'User pressed Cancel or x button
        Exit Do
    ElseIf Len(vInput) > 0 Then 'User has entered non-blank value
        Range("P" & Rows.Count).End(xlUp).Offset(1).Value = vInput
        Exit Do
    End If
Loop

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Try the next approach, please:

Sub testInputBoxValidate()
  Dim strInpB As String
  strInpB = InputBox("INTRODUCE ACT JUSTIFICATIV")
  If strInpB = "" Then MsgBox "You did not introduce anything!": Exit Sub
  Range("P" & Rows.count).End(xlUp).Offset(1).Value = strInpB
End Sub

And a variant to oblige the user inputting something:

Sub testInputBoxValidate()
  Dim strInpB As String
Start:
  strInpB = InputBox("INTRODUCE ACT JUSTIFICATIV")
  If strInpB = "" Then MsgBox "You did not introduce anything!": GoTo Start
  Range("P" & Rows.count).End(xlUp).Offset(1).Value = strInpB
End Sub

I would also suggest you to try some more other checking. For instance, that 'ACT JUSTIFICATIV' must not have a specific length (number of digits)? A specific pattern like `xxxx###', where '###' is a three (x) digits number?

Upvotes: 1

Tom
Tom

Reputation: 9878

Set your Users input to a variable and then test to see whether it is valid or not.

Dim usrInput as String

Do
    usrInput = InputBox("INTRODUCE ACT JUSTIFICATIV")

    If usrInput = vbNullString Then
        MsgBox "Your Error Message"
    End If
Loop While usrInput = vbNullString

Upvotes: 1

FunThomas
FunThomas

Reputation: 29171

I don't think that you can force the InputBox to close only if something was entered. Instead, read the result into variable and loop until something valid was entered

Dim answer as string, okay As Boolean
answer = ""
okay = False
Do While Not okay
    answer = InputBox("INTRODUCE ACT JUSTIFICATIV")
    On Error Resume Next
    okay = Range(answer & "1").Address <> ""
    On Error GoTo 0
Loop

Now, the InputBox will pop up until a valid column was entered.

Upvotes: 1

Related Questions