Prevent InputBox to be empty

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
53
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 !

VBA Code:
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

VBA 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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
One method
Adapt to your needs

VBA Code:
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Try this too.

Code:
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

Board Regular
Joined
Feb 11, 2018
Messages
53
One method
Adapt to your needs

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Here is the solution that VBA purists may prefer (using the "safer" Application.InputBox method)

VBA Code:
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
 

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
53
Try this too.

Code:
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top