VBA: inputbox and cancel button

Question

varInput = Application.InputBox("Text", "Title", Type:=2)
If varInput = "False" Then
    Exit Sub
End If

If the cancel button is pressed, the return value is a string with "False". But on some computer with german setting I will get "Falsch" !

How should be this handled?

Here you can read how to from @Pᴇʜ. The question has nothing in common with yours, but part of the code handles this. See if it helps. — Damian
– Damian, Commented Apr 1, 2019 at 10:47

Pᴇʜ · Accepted Answer · 2019-04-01 11:45:51Z

4

You must always also test the type of the variable to be boolean: VarType(varInput) = vbBoolean when using the Application.InputBox.

Dim varInput As Variant
varInput = Application.InputBox("Text", "Title", Type:=2)

If VarType(varInput) = vbBoolean And varInput = False Then
    Exit Sub
End If

If you test only for False …

If varInput = False Then

… then it will also exit sub if you enter 0 or Falsch (in german Excel) or False (in English Excel).

This hapens because a string of 0 or "Falsch" (or "False") automatically casts into a boolean if compared with False. But only the Cancel button returns a true boolean.

edited Apr 1, 2019 at 11:45

answered Apr 1, 2019 at 11:40

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary's Student Over a year ago

Very nice approach.

Patrick Honorez · Accepted Answer · 2019-04-01 10:53:09Z

2

Remove the quotes:

If varInput = False Then

answered Apr 1, 2019 at 10:53

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

3 Comments

Meloun Over a year ago

In that case it works if I press cancel, but it fails if I write something and press OK. The varIpunt is than string containg e.g. "test" and it fails on the modified line.

Gary's Student Over a year ago

@Meloun Explicitly declare varInput as Variant rather than String.

Pᴇʜ Over a year ago

@patrick you must also test for VarType(varInput) = vbBoolean otherwise it will exit sub also on values like 0 or Falsch (in OP's case). See my answer.

Collectives™ on Stack Overflow

VBA: inputbox and cancel button

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related