Range and #VALUE!

What I'm trying to learn to do is to use Case statements.
In the example below, (I don't know how close I was in the structure) my objective is to evaluate L9 based upon several conditions that will generate an error after the function has been calculated. The conditions are L9 cannot be nonnumeric or a #VALUE! error is generated and L9 ≤ 0 ≥ or a #NUM! error is generated. I would like to be able evaluate L9 telling the user a value (s) is incorrect before the user goes to the next cell, if possible. Else I'm open for suggestions.
I don't know if I'm in the right event or if there needs to be two events - one for Worksheet_Change and another for Worksheet_Calculate.
Note, I only show one MsgBox in the code below. I would have to show a second one for the #NUM! error.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Results

Select Case Results
Case Is Range(“L9”).NumberFormat= 
IF L9 returns a #Value! Error value
Msgbox(“Invalid value for Significance – must be numeric!”
Exit Sub
End Select
End Sub

Open in new window

Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Two things:

First, Select...Case is best used when there are more than two possible test conditions.  Say, if one were testing for "Apples", "Oranges", "Pears", "Bananas", and then anything else.  Your example above really only tests for two possible states:
a) non-numeric value
b) zero
So, in this case, I'd recommend If...Then...ElseIf.... like so:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Results As Variant
    Results = Range("L9").Value
    If Not (IsNumeric(Results)) Then 'not a number
        MsgBox "Invalid value for Significance – must be numeric!", vbCritical
    ElseIf Results = "0" Then 'zero character not allowed
        MsgBox "Invalid value for Significance - must not be zero!", vbcritial
    End If
End Sub

Open in new window

Secondly, the syntax for a Select...Case statement is usually along the lines of
    Select Case Results
        Case 0
        Case 1
        Case 2
        Case 3 To 10
        Case Is > 10
    End Select

Open in new window

where one is testing a variety of possible values or ranges of values for a specific variable.  That really isn't practical here.  You could of course test for zero, then have another test for alphanumeric content, but the statement
Not (IsNumeric(Results))
will only return TRUE or FALSE, not the actual value contained in Results (L9).

Hope that helps,
You may want to look into the basics of the "select case" statement to understand how it should be formatted. Here's a good link: Select Case Statement

Here's a little code to get you started:

Sub Test()
Dim varInput As Variant

varInput = Range("L9").Value

Select Case varInput
    Case CVErr(xlErrValue) '#VALUE! error
        MsgBox ("Invalid value for Significance – must be numeric!")
    Case CVErr(xlErrNum) '#NUM! error
        MsgBox ("Invalid value for Significance – must be greater than zero!")
    Case Else
        MsgBox ("Input is OK")
End Select

End Sub

Open in new window

See Chip Pearson's page for information on the CVErr function: Returning Errors from VBA

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Two options for the same problem! That's great.
Good work - thank you knidly
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Frank FreeseAuthor Commented:
Thank you very much
Glenn RayExcel VBA DeveloperCommented:
Glad we could help.  Always good to see various approaches to a solution; Excel & VBA are good about that.
Frank FreeseAuthor Commented:
Got a problem with your code. I've attached the w/b and the w/s I'm working with is labeled "CONFIDENCE"
When the w/s is activated I set values to ranges L9:L11 and clear any values in R11,O13:P13: and R13:S13. This sets up the w/s for a user to use provided data. There is a command button labeled "Try Again" that is assigned to a macro. This will clear all the data so the user can enter their own if the wanted to (I haven't finished that yet).
If you look under "Remarks" in the w/s you will find that certain values are not excepted in certain cells (L9:L11 - I'm working only on L9 at this time) w/o creating an error. If the user elects to change any values in L9:L11 the worksheet_change event is triggered. In that event I have call a module labeled "UserInput" ( this is your code 'slightly' modified).
I am looping through the Workbook_Change event in call "UserInput".
The best way to see what's happening would be enter in L9 text such as your name. I place a break in the modUserInput module and stepped through it.
Would you prefer a new question "Looping"?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.