How to create macro that selects current date when "COMPLETED" is selected from drop down list

0

I am currently trying to devlop a new excel sheet using macros to automatically include correct dates when a specifc cell has a value.

i have used a code column "M" which works correctly but my column "O" needs changing. I only want the date to populate in column "O" when the drop down list in column "N" shows completed.

How can i change the current code to do this?

see below for current code I am using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2017/10/12
Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("L4:L1048576")) Is Nothing) Then _
Target.Offset(0, 1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("L4:L1048576"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Date
Next
End If
Application.EnableEvents = True
End If


On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("N4:N1048576")) Is Nothing) Then _
Target.Offset(0, 1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("N4:N1048576"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Date
Next
End If
Application.EnableEvents = True
End If
End Sub
Answer
Discuss

Answers

0

No, your column L doesn't quite work as it should. I haven't encountered the use of Target.Dependents in this context before and, frankly, don't know if it works to limit the number of cells to one. But if it does, there should be no need for a loop that writes to many cells. While that loop is in the code you should expect it to do work at one time or another - probably when you least expect it - and then write the date to a number of cells which, you are quite sure, you don't want.

Anyway, I have re-written your code and added comments to explain how it works. One of the changes I have made is to prevent the code from writing dates in the million or so blank rows below the last entry in the list. My code won't fire if triggered by a change below the last used row in column A (change as required). My code will also delete an existing date in column O if the value in column N isn't "completed", allowing a change in column N.

Here is the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Variatus @TeachExcel on 14/12/2019

    Dim Skip As Boolean
    Dim R As Long

    With Target
        ' CountLarge avoids an error
        If (.Cells.CountLarge > 1) Or (.Row < 4) Then Exit Sub

            ' take the last used cell from column A
            If (.Row > Cells(Rows.Count, "A").End(xlUp).Row) Then Exit Sub

            ' 12 and 14 specify columns L and N respectively
            If (.Column = 12) Or (.Column = 14) Then
                Application.EnableEvents = False
                ' another way of specifying column 14
                If .Column = Columns("N").Column Then
                    .Offset(0, 1).ClearContents
                    ' case-insensitive comparison:
                    ' if StrComp = 0 Skip will be False, else True
                    Skip = CBool(StrComp(.Value, "completed", vbTextCompare))
                End If
                If Not Skip Then .Offset(0, 1).Value = Date
                Application.EnableEvents = True
            End If
    End With
End Sub

Note: If an auto-date was already added in column O and the user selects "completed" again the date previously shown will be replaced with a new one. Same for column M. Excel records a "Change" even if the cell value isn't changed in the modification process. If you don't want to allow your users to change the dates in this fashion once they have been entered you can use code to prevent that. You could use another procedure to disallow modifying the cells in columns M and O even directly.

Discuss

Discussion

Thank you for the response.
I have used your code shown above but it doesn't seem to achieve the required result? I have copied the code & now when i select a value in colum L or a drop down option in column N then no dates automatically populate.
SJM4322 Jan 7, '20 at 10:28 am
Please read your original question. You never described what you want. Your original code reacts to a change and the code I wrote does react to a change in column N. Reacting to a click (selection) is possible but doesn't make immediate sense to me and you mention this requirement for the first time now. My code doesn't react to a change in column L. You haven't mentioned such a requirement. To start at the beginning, I have now attached a copy of my workbook, with my code correctly installed, to my original answer above. Try it and let me know what else you need.
Variatus (rep: 3523) Jan 7, '20 at 8:50 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login