CODEWITHSUNDEEP
vbaexcelinputbox
AF721
AF721

Reputation: 23

InputBox: Only allow certain range of values

I want to limit the range of accepted values that users can input.

For instance, I want to allow 0-100 only, and if they enter over 100, then

  1. automatically enter a default value (such as 10) and
  2. create a pop-up message indicating that the default value was applied.

Here's what I have so far:

Dim CO2PriceBox As Variant
    CO2PriceBox = InputBox("Please Enter CO2 Allowance Price ($/ton)", "Enter CO2 Allowance Price", 0)
    Range("C11").Value = CO2PriceBox

Upvotes: 1

Views: 5051

Answers (2)

user3598756
user3598756

Reputation: 29421

you could use Excel InputBox() method to build a little "wrapper" function:

Function GetValue(prompt As String, title As String, minVal As Long, maxVal As Long, defVal As Long) As Variant
    GetValue = Application.InputBox(prompt & "[" & minVal & "-" & maxVal & "]", title, Default:=defVal, Type:=1)
    If GetValue < minVal Or GetValue > maxVal Then
        GetValue = defVal
        MsgBox "your input exceeded the range: [" & minVal & "-" & maxVal & "]" & vbCrLf & vbCrLf & "the default value (" & defVal & ") was applied", vbInformation
    End If
End Function

and use it as follows:

Option Explicit

Sub main()

    Range("C11").Value = GetValue("Please Enter CO2 Allowance Price ($/ton)", "Enter CO2 Allowance Price", 0, 100, 10)

End Sub

Upvotes: 1

gizlmo
gizlmo

Reputation: 1922

I would do it like this:

Dim CO2PriceBox As Variant

CO2PriceBox = InputBox("Please Enter CO2 Allowance Price ($/ton)", "Enter CO2 Allowance Price", 0)

If Not IsNumeric(CO2PriceBox) Or CO2PriceBox < 0 Or 100 < CO2PriceBox Then 'If value out of specified range

    CO2PriceBox = 10 'Default value
    MsgBox "You Entered a wrong value, using default", vbOKOnly
End If

Upvotes: 1

Related Questions