Prevent InputBox to be empty

[otrava18]

Hello ,

I don`t find a solution which is working in a situation. I have two sheets, let`s say A and B. In sheet A i have a button that it sends me to sheet B. When i click on the button i need to receive an InputBox where to enter my name. Everything is ok until now. If i just click ok or cancel with or without enter something in the Inputbox the sheet B is opened.
The question is, how can prevent no data input ? If nothing is entered i want to don`t be able to go to the next sheet. I have tried two ways but without success. Can you help me with an idea ?

Thank you !

SUB NAME()
Dim myName As Variant
myName = InputBox("Whats your name?", "INFORMATII")
Worksheets("sheetB").Range("A1").Value = myName
If IsEmpty(Worksheets("sheetB").Range("A1").Value) = True Then
Worksheets("sheetB").Activate
Else
Worksheets("sheetA").Activate
END SUB

And the second code

SUB NAME()
Dim myName As Variant
myName = InputBox("Whats your name?", "INFORMATII")
Worksheets("sheetB").Range("A1").Value = myName
If myName <> False Then
Worksheets("sheetB").Activate
Else
Worksheets("sheetA").Activate
END SUB

 

[Yongle]

One method
Adapt to your needs

Sub Test()
    Dim myName As Variant
    Do
        myName = InputBox("Whats your name?" & vbCr & "Min 4 characters", "INFORMATII", myName)
    Loop Until Len(Trim(myName)) >= 4
    
End Sub

 

[daverunt]

Try this too.

ValidEntry = False

Do
myNme = InputBox("Enter your name", Title:="Enter Name")

If myNme <> "" Then
ValidEntry = True
Worksheets("sheetB").Activate
Worksheets("sheetB").Range("A1").Value = myNme

Else
Worksheets("sheetA").Activate
End If

    Loop Until ValidEntry

End Sub

 

[otrava18]

One method
Adapt to your needs

Sub Test()
    Dim myName As Variant
    Do
        myName = InputBox("Whats your name?" & vbCr & "Min 4 characters", "INFORMATII", myName)
    Loop Until Len(Trim(myName)) >= 4
   
End Sub

Hello Yongle,

It works partially fine and i need to say thank you but i have an issue. If i press cancel, nothing it happens, the input box is not closing and it wait for that 4 characters. Can i close the inputbox if i press cancel ?

 

[Yongle]

Sub Test()
    Dim myName As Variant
    Do
        myName = InputBox("Whats your name?" & vbCr & "Min 4 characters", "INFORMATII", myName)
        If StrPtr(myName) = 0 Then End
    Loop Until Len(Trim(myName)) >= 4
End Sub

 

[Yongle]

Here is the solution that VBA purists may prefer (using the "safer" Application.InputBox method)

Sub Test2()
    Dim myName As Variant
    Do
        myName = Application.InputBox("Whats your name?" & vbCr & "Min 4 characters", "INFORMATII")
        If VarType(myName) = 11 Then End
    Loop Until Len(Trim(myName)) >= 4
End Sub

If you are interested read this

What are the benefits and risks of using the StrPtr function in VBA?

While looking for a way to test when a user cancels an InputBox, I stumbled across the StrPtr function. I believe it checks if a variable was ever assigned a value and returns zero if it was never

stackoverflow.com

 

[otrava18]

Try this too.

ValidEntry = False

Do
myNme = InputBox("Enter your name", Title:="Enter Name")

If myNme <> "" Then
ValidEntry = True
Worksheets("sheetB").Activate
Worksheets("sheetB").Range("A1").Value = myNme

Else
Worksheets("sheetA").Activate
End If

    Loop Until ValidEntry

End Sub

Hello daverunt,

Thank you for your help. You're code has the same issue like the the first code. Yongle`s code it was good right now, hw insert a line that work. Thank you very much for your help !

 

[daverunt]

Sorry. I misunderstood.
I read it as though you wanted to force some entry into the InputBox when the user cancelled or left it empty.