lock adjacent cell conditional on cell value

[ShoshanaM]

I have a spreadsheet I am developing that has 100 columns (320 rows in each column (row 3 through 323) of drop down lists with 2 values each, "Data", and "No Data" are the values. Each column If the user selects "No Data" for any of those cells, I want the adjacent cell to be locked. I have some code that works for an individual cell; but I need a universal macro that will work for any of the 100 x 323 cells. Plus the code I am using seems to take a really long time for what it does. The code I have is

Please Login or Register  to view this content.

[Leith Ross]

Hello ShoshanaM,

The Target argument is the cell or range of cells that have been selected. The macro now checks that the cell that changed is the range "E3:E323". Additionally, only a single cell is allowed to be selected.

The reason your macro took so long was each time you change the value of the cell in column "F", the Change event was called again. To prevent this from happening, I added the additional statement, Application.EnableEvents, to prevent this from happening.

Please Login or Register  to view this content.

[ShoshanaM]

Thanks Leith, this is very helpful. I will try it. Meanwhile, any ideas on how to make it even more universal so I don't have to have a macro for each of my 100 columns?

[Leith Ross]

Hello ShoshanaM ,

Can you give an example of what you mean?

[ShoshanaM]

This whole thing started with checkboxes and I had a universal check box macro that worked fabulously. Unfortunately, it appears Excel 2010 cannot handle a lot of checkboxes. It all worked great in 2003 and crashes in 2010. Hence the move to the drop down list.

[Leith Ross]

Hello ShoshanaM ,

Do you want the macro to automatically find the end of a range for a column?

[ShoshanaM]

Hmm.. good question. Some users will use through row 323. Some will not. The spreadsheet is designed to accept data through row 323.

[ShoshanaM]

The spreadsheet is designed to collect answers on 100 questions. Each question has its own 2 columns. For example, column E is a drop down where the user can choose whether he/she has data available for question 2 ("Data") or does not have data available for question 2 ("No Data"). If the user chooses "No Data", I want the adjacent cell in column F to record a missing value of ".N" and then be locked so the user doesn't accidentally put other data in that cell. Likewise column G has the drop down for question 3 and column H has a cell to accept the data for question 3. All data is numerical. Each user will be answering the 100 questions somewhere between 1 and 323 times (depending upon the size of the group he/she is in charge of). Does that make more sense?

[Leith Ross]

Hello ShosanaM,

So there are 100 columns (50 pairs) with 300 rows each?

[ShoshanaM]

No, 100 pairs of columns (actually 101) with 300 rows each.

[Leith Ross]

Hello ShoshanaM,

This version extends from column E to GV or 200 columns. Only odd columns are allowed to be changed: E,G,I, etc. The end of each column pair can be different. The macro finds the end of each column pair and tests if the cell is within this range. If not, you guessed it, it exits.

Please Login or Register  to view this content.

[ShoshanaM]

Leith, you are my hero! Thank you that code does exactly what I want it to.

[ShoshanaM]

Leith, The code works great; but seems to be interfering with the conditional formatting I had set in the spreadsheet. I don't suppose you have a quick fix for that?

[Leith Ross]

Hello ShoshanaM ,

It is probably due to the sheet being protected. The cells with conditional formatting should be "unlocked" before the sheet is protected.

[ShoshanaM]

Nice thought, but that's not it. The cells are not locked and the formatting "works", but only after I scroll back and forth across the cells. It is as if the scrolling is updating the cells.

[Leith Ross]

Hello ShoshanaM ,

I am not sure why that is happening. Can you post the workbook for review?

[ShoshanaM]

I can only attach a piece of it; but it includes the code and the conditional formatting.

[ShoshanaM]

I was working on this in 2010 this morning. I have now reopened in excel 2003 and the problem seems to have disappeared. It seems to me I saw a posting somewhere about conditional formatting issues between 2003 and 2010. Maybe that's the issue.