dropdown validation & events

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I don't think this is possible, from what I have read in excel97.... :

I have a function that loops through a config table and finds a list of
unique names, (ie. Tom, Mary, Joe) and copies then into a range of cells.
This range is then specified as the validation range for a user 'dropdown'
called Name.

I also have another function which takes the Name, and builds a list of
projects the 'Name' has worked on to go into a another range for validation
for a 'Project' dropdown

To call the both functions I have one 'update' button.

My manager thinks this is confusing, (you have to press update, chose an
name, press update, chose a project) and like this to be event driven from
the selection being made for the Name by the user. I don't think this is
possible, something to do with events not recognising anything that is
subject to validation.

Does any one have any workarounds or a worked solutions to this problem?

Thanks

Tom
 
Tom
I believe that '97 is such that the Worksheet_Change event will not fire
if the Target cell (the cell that changed)is a Data Validation cell. Later
versions do. But you can get around that by using the Worksheet_Calculate
event. But you need to force an automatic calculation.
Say your Data Validation cell is B1. In some out of the way cell, on
the same sheet, type "=B1" without the quotes. This will force a
recalculation of the sheet whenever B1 changes. HTH Otto
 
I can never remember this for xl97, either. But Debra Dalgleish has some notes
at her site--so now I don't have to remember.

http://www.contextures.com/xlDataVal08.html

It says:

In Excel 97, selecting an item from a Data Validation dropdown list
does not trigger a Change event, unless the list items have been typed in
the Data Validation dialog box. In this version, you can add a button to
the worksheet, and run the code by clicking the button. To see an
example, go to the Sample Worksheets page, and under the Filters
heading, find Product List by Category, and download the
ProductsList97.xls file.

To the OP: She has a couple of workarounds, but I think that use an additional
button.

You may want to see if her implementation is less confusing than what you have.

An alternative may be to use a Combobox from the Control Toolbox toolbar. (or
even a dropdown from the Forms toolbar).
 
Back
Top