Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
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 On Feb 22, 6:49 pm, "jonco" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
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 On Feb 22, 7:20 pm, "John Coleman" wrote: 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 On Feb 22, 6:49 pm, "jonco" wrote: 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.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
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" wrote in message ups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to validate that input was entered into Input Box
Chip,
Brilliant .. And so elegant. I love it. Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to validate input based upon values in two other cells | Excel Discussion (Misc queries) | |||
Validate user input with specific date format | Excel Programming | |||
Input Box Help - Validate Date Format | Excel Programming | |||
TEXTBOX - VALIDATE DATE INPUT | Excel Programming | |||
How can I validate data input by macros? | Excel Programming |