Validation Question

[RJ Swain]

First off, thank you all for your assistance with this question.

I am making a database and in a cell I am using the validation list option
for the following selections:
SAT, UNSAT, N/A

My question is this, if the user selects N/A is there anyway for a set of
cells to blank out, so they are not part of another calculation?
example:
cells: M1 N1 O1 P1 Q1 R1
SAT 4 4 2 2.00 100%
N/A - - 2 - -

I would like the N/A option from the list to blank out cells so when we
calculate percentages the N/A catergories will not affect the overall totals.

 

[ryguy7272]

Most of what you need to know about Data Validation is right here:
http://www.contextures.com/xlDataVal01.html

Regards,
Ryan--

 

[RJ Swain]

The list I know how to make, I wanted to know if the user was to select
something on the list like N/A, how I can make it wipe out certain values in
other cells.

 

[JP]

A Worksheet Event could handle this. This code goes into the Sheet
module where your data resides. See http://www.rondebruin.nl/code.htm
for placement help.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Value = "N/A" Then
Range(Target.Offset(0, 1), Target.Offset(0,
2)).Cells.ClearContents
Range(Target.Offset(0, 4), Target.Offset(0,
5)).Cells.ClearContents
End If
Application.EnableEvents = True
End Sub


HTH,
JP

 

[RJ Swain]

What would I need to put in for range?

A Worksheet Event could handle this. This code goes into the Sheet
module where your data resides. See http://www.rondebruin.nl/code.htm
for placement help.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Value = "N/A" Then
Range(Target.Offset(0, 1), Target.Offset(0,
2)).Cells.ClearContents
Range(Target.Offset(0, 4), Target.Offset(0,
5)).Cells.ClearContents
End If
Application.EnableEvents = True
End Sub


HTH,
JP

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "A1:A10" .adjust to suit your needs

Dim cell As Range
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Value = "N/A" Then
Range(Target.Offset(0, 1), Target.Offset(0, _
2)).Cells.ClearContents
Range(Target.Offset(0, 4), Target.Offset(0, _
5)).Cells.ClearContents
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[JP]

I'm not sure what you mean. You would need to specify what cells you
wanted to clear. I used your sample data above to construct the
example -- if you entered "N/A" in M2, it would clear out N2, O2, Q2
and R2. If you posted some of your actual data, someone could be of
more specific assistance.

--JP

 

[RJ Swain]

I found the error, the line needed to be brought up so the command would
work. So it is wiping out the boxes but is the a line I can add that will
bring back info if the user selects N/A in error and reselects SAT?

 

[JP]

Not easily. By the time the Change event fires, the target cell has
already been changed.

The event can't tell the difference between when you type "SAT"
because you are changing it from "N/A", or when you are selecting it
from a previously empty cell.

If you wanted an "undo" feature, you would need to store what was
currently in those cells in some string variables, then set the cell
values back to those string variables' values.

(See http://j-walk.com/ss/excel/tips/tip23.htm for more information
about undoing.)


HTH,
JP