Force certain Values in Input Box

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
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:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top