Force certain Values in Input Box

[howard]

I have the following code below

Sub Input_Data()

 Sub Input_Data ()
 Dim myValue As Variant
myValue = InputBox("Enter The net income amount")
Range("B3").Value = myValue
myValue = InputBox("Enter Tax rate")
Range("F4").Value = myValue
End Sub

I would like the following amended

Range("B3").Value = myValue -this must force the user to only enter a whole number for eg 5000, 6500 etc

Range("F4").Value = myValue -this must force the user to enter a % for eg 30%, 45% etc


Your assistance in this regard is most appreciated

 

[Rick Rothstein]

See if this code does what you want...

[table="width: 500"]
[tr]
	[td]Sub Input_Data()
  Dim myValue As Variant
  Do
    myValue = InputBox("Enter The net income amount")
    If myValue Like "*[!0-9]*" Then MsgBox "Whole numbers only!"
  Loop While myValue Like "*[!0-9]*"
  Range("B3").Value = myValue
  Do
    myValue = InputBox("Enter Tax rate")
    If Right(myValue, 1) <> "%" Or myValue Like "*[!0-9.%]*" Or myValue Like "*.*.*" Then MsgBox "That is not a valid percentage!"
  Loop While Right(myValue, 1) <> "%" Or myValue Like "*[!0-9.%]*" Or myValue Like "*.*.*"
  Range("F4").Value = myValue
End Sub[/td]
[/tr]
[/table]

 

[DanteAmor]

something like this

Sub Input_Data()
    Dim myValue As Variant
    Do While True
        myValue = InputBox("Enter The net income amount")
        If myValue = "" Then
            Exit Sub
        Else
            If Not IsNumeric(myValue) Then
                MsgBox "Enter number"
            ElseIf myValue - Int(myValue) <> 0 Then
                MsgBox "Enter a whole number"
            ElseIf myValue <= 0 Then
                MsgBox "Enter positive value"
            Else
                Exit Do
            End If
        End If
    Loop
    Range("B3").Value = myValue
End Sub

 

[howard]

Thanks Rick. Code works perfectly

 

[howard]

Thanks for the help, Dante

 

[DanteAmor]

Thanks for the help, Dante

I'm glad to help you. Thanks for the feedback.

 

[Steve_]

See if this code does what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]If myValue Like "*[!0-9]*" Then...
[/TD]
[/TR]
</tbody>[/TABLE]

I hate to admit it, but i have not seen that one before. Impressive.

 

[Rick Rothstein]

I hate to admit it, but i have not seen that one before. Impressive.

The Like operator allows for some very powerful pattern testing... nowhere near what Regular Expressions can do, of course, but still quite powerful in its own right. That particular pattern is looking for any number of character on either side of a non-digit (everything in between square brackets represents a single character). If you look up the Like operator in the help files, it will give you the full story as to what kind of patterns you can use it with to match text.

 

[Steve_]

The Like operator allows for some very powerful pattern testing... nowhere near what Regular Expressions can do, of course, but still quite powerful in its own right. That particular pattern is looking for any number of character on either side of a non-digit (everything in between square brackets represents a single character). If you look up the Like operator in the help files, it will give you the full story as to what kind of patterns you can use it with to match text.

WAY ahead of you. Reading all about it now. Cannot believe I have coded VBA for this many flippin years and never ran into the Like operator.


I have been using inStr inside of do/loops to filter out unwanted characters for years. Thats over.