Range and #VALUE!
Folks,
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.
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!”
Worksheets(“CONFIDENCE”).Range(“L9”).ClearContents
Range(“L9”).select
Exit Sub
End Select
End SubSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much
Glad we could help. Always good to see various approaches to a solution; Excel & VBA are good about that.
ASKER
Glenn,
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"?
Excel-Statistics.xlsm
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"?
Excel-Statistics.xlsm
ASKER
Good work - thank you knidly