Dependent List problem

**jennieS** · 04-09-2009, 08:59 AM

I've got a table which has 2 columns and dependant lists created which work fine when the first item is selected then the second item is selected based on the first column. The problem is when the first column is then changed, it doesn't clear the data in the second column. Example file attached - so if at first Fruit, Apple is selected, then Fruit is changed to Vegetable, it still displays Apple in the second column. Is there any way of clearing the second column if the first is changed?
Thanks

**mdbct** · 04-09-2009, 09:21 AM

Right clink on the tab and choose View code. Paste the following in the code window.

Please Login or Register  to view this content.

**jennieS** · 04-09-2009, 09:25 AM

Brilliant, thank you!

**SuperFly** · 01-08-2010, 08:26 AM

Hi All

This code works brilliantly, but how do I modify it to delet cell 4 and 5 as well?

Sorry for a silly question, I know nothing about code.

Thanks

**SuperFly** · 01-08-2010, 08:48 AM

And now the code is not doing anything.

I have copied and pasted the code again, and still nothing.

Please help!

**mdbct** · 01-08-2010, 10:16 AM

Currently the macro clears the contents in column 2 of the same row when the value in the column 1 using the Target.Offset(0,1) to determine which column to clear.

The Offset syntax is:
Offset(Move # row, Move # of columns) where positive numbers are to the down and to the right while negative numbers are up and to the left of the referenced cell.

I'm not entirely sure what you mean by cells 4 and 5. If you mean columns 4 and 5 for the same row use:

Please Login or Register  to view this content.

**SuperFly** · 01-08-2010, 12:09 PM

Hi mdbct

Thanks for the code, it is exactley what I needed. That works like a dream! Cheers!

Do you know what other possible problems I might have using Dependant lists? Its the first time I am using them, and I am creating it for a stock take, so I need the integrity of the information to be 100%. Therefore, if I could minimse the possible problems, it would be awesome.

I also want to be able to give the user's promt's in terms of the data they are about to select for.

Scenario

My Column Headings are going to just be Desc1, Desc2, Desc3 etc, reason being, the primary choice they make is going to dictate the way an item is described. Eg; If the 1st Primary choice Table, the possible description choice could be Wood, Executive, 3. The 3 is representative of 3 Drawers.

If the 2nd Primary choice on the next row is Bench, the possible description choice could be Wood, Arms, 4. The 4 is representative of 4 Seats.

As the above shows, some descriptions choices are the same in words, but different in meaning, so I need a way to let the user know what information they are about choose. Is there a way I can do this without it getting to messy? Or is this possible at all? Under normal circumstances I would make the options 1 Drawer, 2 Drawers or 1 Seater, 2 Seater etc, but the data is going to be uploaded into our official system and the description Cells has a short character space allowance. (the people using the data on the day to day basis will understand what the information is representative of, but the stock taking team are temps, so are not familiar with the description sequence)

Thanks