fields as multiple switches

Given: an array with (more than) 3 columns. One is an on/off switch. Another is a unique identifier. The third is a list of other unique identifiers "linked" to that row/entry. When a given entry is turned "on",all the items in it's list are also turned on, and vice versa. I'm attaching a simple example, though in the real world the identifiers are code numbers, so they are things like 603.4 and 709.4.2.1.3

Any thoughts on an easyish way to do this? I think of using a macro associated with cell/worksheet changes, but then parsing the list and searching seems likely to be slow. Thanks for any help.

Keith

Im really not sure what your question is here? What are you trying to do, and what would a sample answer look like?

Can I try to interpret your requirement:

From the attached file, let's look at row 1.

Cell C1 contains "b, c, f"
If you change A1 to "off", do you want the rows which contain "b", "c" or "f" in column B to also show "off" in column A?

If this IS the case, you'll need some VBA.

Can you confirm this is what you want? If not, can you clarify?

Thanks for the assist her, Olly

If my assumption is correct, then maybe try this, in the worksheet module:

Please Login or Register  to view this content.

*Disclaimer: It is 1:23am, I've been drinking wine for over 6 hours...*

Here's a revised file, showing my assumption and solution.

Try changing this line, so that it will work with numeric values as well as text:

Please Login or Register  to view this content.

To answer your questions:
Disabling Screenupdating improves performance and prevents screen flicker.
Disabling Events prevents the code being called recursively whenever changes are made to the worksheet by the code.
The target.cells.count code does exactly as you say, and means the code only runs if just one cell is being changed.

Hope that helps.

Looping through arrays from Lbound to Ubound is just good practice.

If unspecified, array indices will start at 0, but sometimes it's helpful to specify they start at 1 (or even something else again) instead. If we make an assumption about the lower bound index of an array, it may introduce an error if that is nor as expected. So good practice is to loop from Lbound to Ubound, knowing that we will then make sure we include the full array range

In this case, while you are correct that the split function creates a lower bound of 0, the approach of using lbound means that if we were to change the method of creating our array, we wouldn't have to worry about whether that lower bound value had changed - we know the loop will continue to work.

Hey Olly: I tried to begin to incorporate your code into my existing worksheet_change, and broke it (new thread in VBA forum). But in the meantime, I think I discerned that I'd want to change the

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

to avoid leaving ScreenUpdating and EnableEvents False. But I think my playing with this might have broken something, and I'm not figuring out how to get it working again.

Ooops. My mistake - I coded that poorly. I did warn it was rather late at night!!

Change the initial section to:

Please Login or Register  to view this content.

Then is more than one cell is changed the routine will exit before it changes the application settings.

Your problem may now be that application events remain disabled, so worksheet changes are no longer triggering the code. To correct the problem now, open the immediates window in the VBE (Ctrl-G), and enter the following:

Please Login or Register  to view this content.

Erm, that's what I just suggested...!