I have created a form in excel that users fill out to enter change requests
with various data. One of my columns has drop down lists that are specific to
a hidden sheet that pulls categories and reasons...

If it is this category, it lists this specific list of reasons...

If the user changes its mind on the category, how can I get the reason
selected removed from the cell then they click on a different category.
Currently that specific reason stays in the cell regardless that a different
category has been selected, until they click on a new reason - is there a way
to clear the reason cell when the user selects a different category?
--
Susan M.
Project Controls Data Coordinator

SaM was thinking very hard :
I have created a form in excel that users fill out to enter change requests
with various data. One of my columns has drop down lists that are specific to
a hidden sheet that pulls categories and reasons...

If it is this category, it lists this specific list of reasons...

If the user changes its mind on the category, how can I get the reason
selected removed from the cell then they click on a different category.
Currently that specific reason stays in the cell regardless that a different
category has been selected, until they click on a new reason - is there a way
to clear the reason cell when the user selects a different category?

I do something similar with Expense Categories, Expense Subcategories,
where the value selected in the Expense Categories column determines
what displays in the Expense Subcategories dropdown. If the value in
the latter isn't found in the ExpenseCategories range after the
category is changed then ConditionalFormatting shades the subcategory
cell red. This is how I cue the user that something is wrong about the
action they just did, and so hints to now select the correct
subcategory associated with the category change. I found this to be a
lot more intuitive than to set up an event handler to monitor input
activity; -thay just use up resources and are generally a performance
hit if in a Change event. Since you already have the dropdowns working,
the CF is all you need if this satisfies your needs instead of a VBA
solution. Here's the formula I use in CF:
=InvalidSubcategory

Here's the defined name RefersTo for that:

=AND(Expenses!ExpenseCategory<"",UPPER(Expenses!E xpenseCategory)<"OTHER
EXPENSE",ISERROR(HLOOKUP(Expenses!ExpenseSubCatego ry,INDIRECT(SUBSTITUTE(Expenses!ExpenseCategory,"
","")),1,FALSE)))

Note that the only reason I use the SUBSTITUTE() function is because my
defined name ranges are all concatenated proper case derivitives of the
account name, and so the spaces are removed. (eg: "Office Supplies" as
an account display name is "OfficeSupplies" as the defined name for the
range that contains its subcategories. Otherwise, INDIRECT() will fail
if it tries to process the category with a space (or any other invalid
defined name characters).

The ISERROR() function handles if the subcategory doesn't belong to the
selected category, which if the case is what the HLOOKUP() function
will return.

The 'Other Expense' category doesn't have subcategories, so if it's
selected while something is in the subcategory cell I want it to turn
red as a cue to clear that cell.

And, of course, the Expense Category must already have been selected
for the subcategory dropdown to populate. How this works is as soon as
the user selects an expense category, the subcategories cell turns red
to cue the user that this is the next value to select.

HTH
Garry

Sam:
If you question is just about formulas and not VBA code in Excel VBA forms,
it would probably be better to post it in the Formulas section of the forum.
Hope you find a solution.

--
Rich Locus
Logicwurks, LLC


"SaM" wrote:

I have created a form in excel that users fill out to enter change requests
with various data. One of my columns has drop down lists that are specific to
a hidden sheet that pulls categories and reasons...

If it is this category, it lists this specific list of reasons...

If the user changes its mind on the category, how can I get the reason
selected removed from the cell then they click on a different category.
Currently that specific reason stays in the cell regardless that a different
category has been selected, until they click on a new reason - is there a way
to clear the reason cell when the user selects a different category?
--
Susan M.
Project Controls Data Coordinator