How to validate that input was entered into Input Box

[jonco]

I have 3 input boxes that requests an amount, a Clerk number, and a Text
Note:
:
ActiveCell = InputBox("Amount?")
ActiveCell.Offset(0, 1).Select
ActiveCell = InputBox("Clerk?")
ActiveCell.Offset(0, 1).Select
ActiveCell = InputBox("Note?")

How can I make sure the user puts something into the fiirst two input boxes?
They can't be left blank. The note box can however be blank.

 

[John Coleman]

Maybe something along these lines:

Sub Test()
Dim Amount As Variant
Do
Amount = InputBox("Amount?")
Loop Until Len(Amount) > 0
ActiveCell = Amount
End Sub

Hth

-John Coleman

 

[Greg Glynn]

Jonco,

Don't use InputBoxes. You're much better off with a UserForm. You
can add code to each of your data entry fields on a userform which, on
any change, triggers an event. This event checks to see if Box1 is
not empty and Box2 is not empty, and if true, then Box3 get enabled
for Data Entry.

Similar to this:

Sub CheckAssetOK()
If InfoCard.AssetNoInput.Value <> "" And _
InfoCard.SiteCodeInput.Value <> "" And _
InfoCard.ProductCodeInput.Value <> "" And _
InfoCard.SerialNumberInput.Value <> "" Then _
InfoCard.InfoOK.Enabled = True Else _
InfoCard.InfoOK.Enabled = False
End Sub

Private Sub SerialNumberInput_Change()
CheckAssetOK
End Sub

Private Sub SiteCodeInput_Change()
CheckAssetOK
End Sub

My form is called InfoCard. I have fields called AssetNoInput (for
Asset Numbers), SiteCodeInput, ProductCodeInput, SerialNumberInput.

When ALL of them are <> "", then the OK button gets enabled. You need
to change the code to only enable InputBox3 when the first two have
data.

Hope this helps.

 

[Greg Glynn]

The other advantage a form gives you, is you can add Data Validation
(not supported natively, but you can write code to do it), so if you
need specific types of data or specific strings, you can restrict
entry.

I have a field in one form that requires an Asset Number in the Format
"AA-9999" or "AA-99999". So I can force data entry to match this
mask:

Private Sub AssetNoInput_Change()

AssetNoInput = UCase(AssetNoInput)
For Count = 1 To Len(AssetNoInput)
Select Case Count
Case 1, 2
If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(AssetNoInput,
Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 3
If Mid(AssetNoInput, Count, 1) <> "-" Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 4 To 7
If InStr("0123456789", Mid(AssetNoInput, Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 8
If InStr("0123456789 ", Mid(AssetNoInput, Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 9
AssetNoInput = xAssetNoInput
Beep
End Select
Next
xAssetNoInput = AssetNoInput
CheckAssetOK
End Sub

 

[John Coleman]

Instead of an annoying input box which won't go away, you can be more
polite about it:

Sub MorePolite()
Dim Amount, Clerk

Amount = InputBox("Amount?")
Do While Len(Amount) = 0
Amount = InputBox("Please enter an Amount")
Loop
ActiveCell = Amount
ActiveCell.Offset(0, 1).Select
Clerk = InputBox("Clerk?")
Do While Not IsNumeric(Clerk)
Clerk = InputBox("Please enter a Clerk number")
Loop
ActiveCell = Clerk
ActiveCell.Offset(0, 1).Select
ActiveCell = InputBox("Note?")

End Sub

This approach also guarantees that a *number* is entered for Clerk and
not say a name like "Frank". If the clerk numbers are more than simple
numbers, you might want to revert to a simple Do While Len(Clerk) = 0

Hth

-John Coleman

 

[Chip Pearson]

Greg,

I think you can greatly simplify your code by using the LIKE operator. E.g.,

Dim AssentNoInput As String
AssentNoInput = "AZ-12345"
If (AssentNoInput Like "[A-Z,a-z][A-Z,a-z]-####") Or _
(AssentNoInput Like "[A-Z,a-z][A-Z,a-z]-#####") Then
Debug.Print "OK"
Else
Debug.Print "Bad code"
End If


This will accept as OK a string that begins with two letters (upper or
lower case) followed by a "-" and then either 4 or 5 numeric characters. Any
other string will fail the test. See "Like Operator" in VBA help for more
info.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Greg Glynn]

Chip,

Brilliant .. And so elegant. I love it.

Greg