Question about dates.

[superbeast326]

I have two columns, A and B. Both have 50 rows. Column A is filled entirely with "No". What I want is that the moment I switch a row in Column A to "Yes". I want the row in Column B to store the date that the change was made. Is this possible?

 

[superbeast326]

Thank you very much.

 

[superbeast326]

If I have multiple ranges that I want to check for "Yes" that is generated by a formula and if I want to do the c.value=c.value, how can I do that? Also If I have a bunch of cells with Data Validation Lists that gives me an option between "Yes" and "No", is it possible for me to make the cells that are changed to "Yes" a locked cell, meaning an unselectable one?

 

[Peter_SSs]

If I have multiple ranges that I want to check for "Yes" that is generated by a formula and if I want to do the c.value=c.value, how can I do that?

Try a change like this

Const myRange As String = "A2:A51, A62:A70, J3:J7"

Also If I have a bunch of cells with Data Validation Lists that gives me an option between "Yes" and "No", is it possible for me to make the cells that are changed to "Yes" a locked cell, meaning an unselectable one?

Yes, the code would be along the lines of my earlier Worksheet_Change code since entering a value in a cell by Data Validation drop-down does trigger that event.

However, to lock these cells and make them unselectable, will require the sheet to be protected which would also impact the Worksheet_Calculate code given if we are talking about the same sheet.

Are we talking about the same sheet?

If so, is this Data Validation Yes/No/Lock etc replacing the Worksheet_Calculate code or referring to different cells altogether?

 

[superbeast326]

The first four columns will be populated with the DV Lists, the "Yes" and "No" in the fifth column will be determined by an IF() function that will check for conditions present in another worksheet. The sixth column will return "Yes" in that row, if the other columns are "Yes" as well. So that means for the DV Lists, I want to make it so that, when I change "No" to "Yes", I will not allow myself to change it back, since I am going to protect the worksheet. The seventh column will return the date when that particular row in the sixth column was changed to "Yes". I am going to lock the cells with the formulas in them and keep the cells with the DV Lists unlocked.

 

[Peter_SSs]

1. That did not directly answer either of my last two questions.

The first four columns will be populated with the DV Lists,

2. Does that mean
- they contain values (lists) that will be used in Data Validation cells elsewhere, or
- the values in them will be populated by choosing values from a DV drop-down list?

The sixth column will return "Yes" in that row, if the other columns are "Yes" as well.

3. Does "other columns" mean A:E?

4. And is this column (6th) a formula? If so, then the existing Worksheet_Calculate code, adjusted to column F should do the job, apart from a modification that will be needed because the sheet will be protected.

So that means for the DV Lists, I want to make it so that, when I change "No" to "Yes", I will not allow myself to change it back,

5. Repeating myself I know but confirm this means in columns A:D? If not, where?

I am going to ... keep the cells with the DV Lists unlocked.

6. If you are going to make them unselectable and/or unchangeable the code is also going to have to lock them as they become "Yes". Is that OK?

7. Are we dealing with rows 2:51? If not, more info please.

Please try to address each point carefully.

 

[superbeast326]

1. That did not directly answer either of my last two questions.

2. Does that mean
- they contain values (lists) that will be used in Data Validation cells elsewhere, or
- the values in them will be populated by choosing values from a DV drop-down list?


3. Does "other columns" mean A:E?

4. And is this column (6th) a formula? If so, then the existing Worksheet_Calculate code, adjusted to column F should do the job, apart from a modification that will be needed because the sheet will be protected.


5. Repeating myself I know but confirm this means in columns A:D? If not, where?


6. If you are going to make them unselectable and/or unchangeable the code is also going to have to lock them as they become "Yes". Is that OK?

7. Are we dealing with rows 2:51? If not, more info please.

Please try to address each point carefully.

I apologize for not being clear, the DV lists will be used to populate the cells with "Yes" or "No", they will in columns A:D, rows 2:51, cell E will be changed to "Yes" with a formula. F2, for example, will change to "Yes" when A2:F2 is a "Yes". Column G will then be used for the date during which F2:F51 was changed to "Yes".

 

[Peter_SSs]

OK, try this. I have assumed the Yes/No DV is already set up in A2:D51 as well as the formulas in E2:F51.

1. Unprotect the sheet.

2. Select the whole sheet and remove the checkmark from the cell's Locked' property.

3. Select E2:G51 and add the checkmark to the cell's Locked' property.

4. Repeat step 3 for any other worksheet cells you want to be locked when the sheet becomes protected.

5. Protect the sheet (password optional)

6. Add the folowing codes to the sheet's module. Add the password in 4 places in the code if you have used one.

7. Test.

Note that I have coloured the DV cells as they have been changed to Yes to help with testing. You can remove that line if you don't want the colour.

The code does not stop you selecting a 'Yes' DV cell, but it should stop you changing it.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Const</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "F2:F51"<br><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    ActiveSheet.Unprotect Password:="" <SPAN style="color:#007F00">'<- Password?</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(myRange)<br>        <SPAN style="color:#00007F">If</SPAN> c.HasFormula And UCase(c.Value) = "YES" <SPAN style="color:#00007F">Then</SPAN><br>            c.Value = c.Value<br>            c.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    ActiveSheet.Protect Password:="" <SPAN style="color:#007F00">'<- Password?</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A2:D51"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range(myRange))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.Unprotect Password:="" <SPAN style="color:#007F00">'<- Password?</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Changed<br>            <SPAN style="color:#00007F">If</SPAN> UCase(c.Value) = "YES" <SPAN style="color:#00007F">Then</SPAN><br>                c.Locked = <SPAN style="color:#00007F">True</SPAN><br>                c.Interior.ColorIndex = 35 <SPAN style="color:#007F00">'<- For testing</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        ActiveSheet.Protect Password:="" <SPAN style="color:#007F00">'<- Password?</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[superbeast326]

So that means, if I choose to name each column (A:E), I can substitute the range in the code for the name of the range i.e

 Const myRange="Range1, Range2, Range3, Range4, Range5"

right and if I leave the password part empty, the sheet will not be password protected. Am I correct?

 

[Peter_SSs]

So that means, if I choose to name each column (A:E), I can substitute the range in the code for the name of the range i.e

 Const myRange="Range1, Range2, Range3, Range4, Range5"

right and if I leave the password part empty, the sheet will not be password protected. Am I correct?

Give them a try!

 

[superbeast326]

By the way, why is Ucase used instead of Lcase?