Excel date stamp

[johnlad]

Hi everyone, I was hoping to get some help with a spreadsheet I am creating for work. I have had this partly answered on here but theres a slight issue with it that I hope someone knows the solution to.

I have a spreadsheet with a "Yes" "No" drop down boxes set up in column G. These run down the column to row 16. What I want is for the date that the drop down box is changed to "Yes" to be entered in the cell to the right of that box. So, if the drop down box in cell G2 is changed to "Yes" today (21/02/11) the cell to the right (H2) will enter the date of when that work was completed. I have the code for this working and set up in VBA. It looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And LCase(Target.Value) = "yes" Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Date
    Application.EnableEvents = True
End If
End Sub

The only problem is when I try to change the "Yes" back to "No". When I do this the date stays as it was in the cell next to it. So if a mistake is made and the wrong cell is changed back to "No" the date remains. Does anyone know how I can make the cell to the right of the drop down blank again if it is changed back?

Any help with this will be greatly appreciated.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = IIf(LCase(Target.Value) = "yes", Date, "")
    Application.EnableEvents = True
End If
End Sub

 

[Dryver14]

As a beginner I think "Simple"

why not use

=If("cell to left")="yes",Today(),"")

 

[VoG]

As a beginner I think "Simple"

why not use

=If("cell to left")="yes",Today(),"")

If you open up the workbook tomorrow, that will show tomorrow's date.

 

[johnlad]

Yeah that vba one works thank you for your help. I really appreciate it.

The reason I'm not using cells is because other people who aren't that computer literate will be updating it so I want the code tucked away to avoid them from accidently editing/deleting the code. Thanks for your suggestion though.

 

[johnlad]

Just noticed one last thing. I have multiple columns and have tried adding the code again for another column and changing the column to look at number 10. But because the code is a duplicate it is coming up with errrors. I realise this is because of the "Worksheet_Change" sub being a duplicate. How do I make multiple columns of this code? thanks

 

[VoG]

You need to combine them:

One way

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = IIf(LCase(Target.Value) = "yes", Date, "")
    Application.EnableEvents = True
ElseIf Target.Column = 10 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = IIf(LCase(Target.Value) = "yes", Date, "")
    Application.EnableEvents = True
End If
End Sub

But if you are doing the same thing with both columns then this is neater

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Or Target.Column = 10 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = IIf(LCase(Target.Value) = "yes", Date, "")
    Application.EnableEvents = True
End If
End Sub

 

[johnlad]

Thank you for help again. That works. Been trying learn VBA after starting this spreadsheet just didn't realise how much there was to it

Thank you