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]

Could you please point me to right direction for some VBA Basic Lessons, or better yet could you please explain to me what each line of code means in Pseudo-code.

 

[Peter_SSs]

Could you please point me to right direction for some VBA Basic Lessons,

Another forum member has compiled an extensive list of resources:
http://www.mrexcel.com/forum/showthread.php?t=572285

or better yet could you please explain to me what each line of code means in Pseudo-code.

See if this helps.

<font face=Courier New><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:#007F00">'This is the range we want to record when changed to Yes</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A2:A51" <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <br>    <SPAN style="color:#007F00">'Find where Target (ie the cell(s) that were changed) and our</SPAN><br>    <SPAN style="color:#007F00">'range of interest overlap/intersect and set the</SPAN><br>    <SPAN style="color:#007F00">'range 'Changed' to be this intersection</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range(myRange))<br>    <br>    <SPAN style="color:#007F00">'If 'Changed' isn't nothing, then there must have been an overlap</SPAN><br>    <SPAN style="color:#007F00">' so 1 or more cells in our range of interest has changed</SPAN><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>    <br>        <SPAN style="color:#007F00">'Turn off events because we don't want this whole predure</SPAN><br>        <SPAN style="color:#007F00">'called again if we enter a date in the next column</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <br>        <SPAN style="color:#007F00">'Look at each cell in our range of interest that was changed</SPAN><br>        <SPAN style="color:#007F00">'(could be more than 1)</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Changed<br>            <br>            <SPAN style="color:#007F00">'If it contains a Yes then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> UCase(c.Value) = "YES" <SPAN style="color:#00007F">Then</SPAN><br>                <br>                <SPAN style="color:#007F00">'Put the current date in the next cell to the right</SPAN><br>                c.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br>                <br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>        <SPAN style="color:#007F00">'Check the next changed cell until all have been checked</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <br>        <SPAN style="color:#007F00">'Turn events back on so it is ready to check the next</SPAN><br>        <SPAN style="color:#007F00">'change on the worksheet</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <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><br></FONT>


Apart from the possible issue of changing a 'Yes' cell to something else (that I mentioned in my last post) note that if a cell was changed to Yes yesterday, if somebody re-enters Yes in that same cell today, the current code will update the date to today's date. If that is a problem, the code could be amended to cope. So if that is a possibility you would need to specify if you want the date that Yes was first entered or last entered.

 

[superbeast326]

Actually the "Yes" is going to be generated with a formula based on conditions present throught the workbook. With that, I want the date during which, the row was changed to "Yes".

 

[superbeast326]

Is it possible to lock the cells that have the word "Yes" in them.

 

[superbeast326]

Actually the "Yes" is going to be generated with a formula based on conditions present throught the workbook. With that, I want the date during which, the row was changed to "Yes".

If it isn't, It will be set to "No". Will the fact presented in the previous sentence affect the code?

 

[Peter_SSs]

Actually the "Yes" is going to be generated with a formula based on conditions present throught the workbook. With that, I want the date during which, the row was changed to "Yes".

In that case this worksheet_Change code will not do the job. I did point that out in post #10:

For your circumstances, you could try this. It assumes the values in column A are not the result of formulas.

 

[superbeast326]

superbeast326

You are right that the formula suggested will not record "the date that the change was made" as you requested, it will simply show the current date for any rows where the change has been made at some time in the past.

The link provided in post #4 has some suggestions regarding this sort of thing. For your circumstances, you could try this. It assumes the values in column A are not the result of formulas.

You didn't say if it is possible, or what should happen if you subsequently change a "Yes" value back to "No" or to some other value? My code just leaves column B with the previous date in this circumstance.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window and test.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range

Const myRange As String = "A2:A51" '<- Change to suit

Set Changed = Intersect(Target, Range(myRange))

If Not Changed Is Nothing Then
Application.EnableEvents = False
For Each c In Changed
If UCase(c.Value) = "YES" Then
c.Offset(, 1).Value = Date
End If
Next c
Application.EnableEvents = True
End If
End Sub

Are you referring to this code?

 

[superbeast326]

I apologize for repeating my self, but if I want to lock the cell that has been changed to "Yes", how can I do that and also must the "Yes" be written as "YES" in the cell (based on the code) in order for it to work?

 

[Peter_SSs]

Are you referring to this code?

Yes

.. must the "Yes" be written as "YES" in the cell (based on the code) in order for it to work?

Capitalisation does not matter, it could be written as "YES", "yes", "yEs", "yES", etc.

However, for the code I provided previously to work the value would have to be entered manually or by code, not as the result of a formula. The changing of a formula result does not trigger a Worksheet_Change event.

I apologize for repeating my self, but if I want to lock the cell that has been changed to "Yes", how can I do that ...

If the range in question (eg A2:A51) is going to at some stage change to "Yes" by a formula then you have acouple of options.

1. Use the Worksheet_Calculate event instead of Worksheet_Change. From what I can gather of your circumstances this should work. The possible downside of this is that worksheets can calculate very often and running this code every time may impact on the speed performance of your workbook.

2. Try to trace the formulas in A2:A51 (I assume they are all similar formulas?) back to a cell or number of cells that are manually entered and base a Worksheet_Change event on those cell(s). Can you advise what the formula is in column A that will eventually result in a "Yes"

In either case, if the value is going to be 'locked' to Yes, then that cell will no longer have a formula, it will simply contain the value "Yes". Is that a problem?

 

[Peter_SSs]

To test the Worksheet_Calculate option, in a copy of your workbook, remove the Worksheet_Change code and replace it (in the same module) with the Worksheet_Calculate code below. Then try altering cells that feed into the column A formulas that will cause them to change to "Yes".

<font face=Courier New><br><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:#007F00">'This is the range we want to record when changed to Yes</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A2:A51" <SPAN style="color:#007F00">'<- Change to suit</SPAN><br><br> <br>    <SPAN style="color:#007F00">'Turn off events because we don't want this whole predure</SPAN><br>    <SPAN style="color:#007F00">'called again if the sheet recalculates because of</SPAN><br>    <SPAN style="color:#007F00">'changes we are making here</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <br>    <SPAN style="color:#007F00">'Check each cell in the range</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(myRange)<br><br>        <SPAN style="color:#007F00">'If the cell has a formula whose result is "yes" then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> c.HasFormula And UCase(c.Value) = "YES" <SPAN style="color:#00007F">Then</SPAN><br><br>            <SPAN style="color:#007F00">'Replace the formula with its result (Yes)</SPAN><br>            c.Value = c.Value<br><br>            <SPAN style="color:#007F00">'Put the date in the adjacent cell</SPAN><br>            c.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#007F00">'Check the next cell until all have been checked</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <br>    <SPAN style="color:#007F00">'Turn events back on so it is ready to check the next</SPAN><br>    <SPAN style="color:#007F00">'time the worksheet calculates</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>