Validation Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RJ Swain - 08 Feb 2008 19:06 GMT

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.

Reply to this Message

ryguy7272 - 08 Feb 2008 19:55 GMT

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

Regards,
Ryan--

Signature

RyGuy

> First off, thank you all for your assistance with this question.
>
[quoted text clipped - 11 lines]
> 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.

Reply to this Message

RJ Swain - 08 Feb 2008 20:05 GMT

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.

> Most of what you need to know about Data Validation is right here:
> http://www.contextures.com/xlDataVal01.html
[quoted text clipped - 17 lines]
> > 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.

Reply to this Message

JP - 08 Feb 2008 20:37 GMT

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

> 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
[quoted text clipped - 26 lines]
>
> - Show quoted text -

Reply to this Message

RJ Swain - 08 Feb 2008 20:52 GMT

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
[quoted text clipped - 44 lines]
> >
> > - Show quoted text -

Reply to this Message

Gord Dibben - 08 Feb 2008 23:32 GMT

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

>What would I need to put in for range?
>
[quoted text clipped - 46 lines]
>> >
>> > - Show quoted text -

Reply to this Message

JP - 08 Feb 2008 23:39 GMT

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

> What would I need to put in for range?
>
[quoted text clipped - 12 lines]
> > Application.EnableEvents = True
> > End Sub

Reply to this Message

RJ Swain - 09 Feb 2008 04:50 GMT

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?

> 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
[quoted text clipped - 20 lines]
> > > Application.EnableEvents = True
> > > End Sub

Reply to this Message

JP - 10 Feb 2008 22:49 GMT

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

On Feb 8, 11:50 pm, RJ Swain <RJSw...@discussions.microsoft.com>
wrote:

> 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
[quoted text clipped - 7 lines]
>
> > --JP

Reply to this Message