CODEWITHSUNDEEP
excelvba
Gab
Gab

Reputation: 13

excel checking if user input is within the range

I just started learning about excel macro and I want to create a spread sheet which will error check for upper and lower limit. first setting the limits (user can choose those on one case) and then prompting for three inputs for capacitance, I want to check if those values are within the specified range can someone please point me in the right direction thank you

Upvotes: 0

Views: 1060

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Reading your question, I don't know if you're asking for a range of cells or a range of values, so:

Alternative 1:

This code would check if cells are within range "A1:B3".

you should put it behind the worksheet's code

Where to put it

Adjust it to fit your needs

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim evalRange As Range

    Set evalRange = Me.Range("A1:B3")

    If Not Intersect(Target, evalRange) Is Nothing Then

        MsgBox "User changed something within evaluated range"

    End If

End Sub

Alternative 2

Put this code in a standard vba module, run it with F8 or F5 and adjust it to fit your needs:

Public Sub AskForValues()

    Dim inputValue As Variant
    Dim lowerLimit As Long
    Dim upperLimit As Long

    lowerLimit = 1
    upperLimit = 10

    inputValue = InputBox("Please enter a value")


    If Not IsNumeric(inputValue) Then
        MsgBox "The value you entered is not a number"
        Exit Sub
    End If

    If inputValue < lowerLimit Or inputValue > upperLimit Then
        MsgBox "The value you entered is not within range"
        Exit Sub
    End If

    MsgBox "you entered..." & inputValue

End Sub

Remember to mark the answer if it helps.

Upvotes: 1

Related Questions