Force certain Values in Input Box

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,386
I have the following code below

Sub Input_Data()

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
See if this code does what you want...
Code:
[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

Well-known Member
Joined
Dec 3, 2018
Messages
9,679
Office Version
2007
Platform
Windows
something like this

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

Well-known Member
Joined
Jun 26, 2006
Messages
5,386
Thanks for the help, Dante
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
See if this code does what you want...
Code:
[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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
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_

Board Regular
Joined
Apr 28, 2010
Messages
167
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.
 

Forum statistics

Threads
1,085,513
Messages
5,384,120
Members
401,881
Latest member
Dato

Some videos you may like

This Week's Hot Topics

Top