Force certain Values in Input Box

howard

Well-known Member
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
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
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
 

Steve_

Board Regular
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top