Automatically Insert Date and Time

[Chris Martin]

I have been trying to automatically insert the date in collumn B, when something is entered in collumn A. (Collumn is a drop-down menu, but I wouldn't have thought this would affect anything). I tried to investigate this on the website, and managed to find a solution (http://www.mrexcel.com/archive/Dates/29888.html). It was all working fine, but then I just formatted the spreadsheet and did a couple of things to it, and when I re-opened the spreadsheet to use it, the date/time was not entered. The coding still appeared to be correct. To investigate the problem, I tried to insert a new sheet in the workbook, and in the second sheet, the coding would not work, so I must have made a change to the workbook, rather than just the sheet (the coding works when I create a new workbook). Does anyone have any ideas as to what I may have done to stop it working? Or how to clone the contents of the sheet so I can try again? Or just any other suggestions? Thanks!

 

[Datsmart]

The code you are referrencing is Worksheet event code. Is your code exactly like the example on the linked page?
Can you post an example here?
What worksheet module do you have the code in?

 

[erik.van.geit]

Hi, Chris Martin
Welcome to the Board !!!!!

you might have used some code which disable the events
doesn't it work when you close and reopen excel ?
or
run this code

Sub foo()
Application.EnableEvents = True
End Sub

does it work now ?

you might add a testline onto start the procedure

MsgBox "changecode running"

there should be a popup when you change something to the sheet

kind regards,
Erik

 

[Chris Martin]

The code I am using is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  'Written by Barrie Davidson
  If Target.Column = 1 Then
    Target.Offset(0, 1).Value = Now()
  End If
End Sub

I also tried using:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    ' Only look at single cell changes
        If Target.Count > 1 Then Exit Sub
        Set rng = Range("A:A")
        ' Only look at that range
        If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Add the Date in Column C whenever an entry is made in the same row in Column A
            If Target <> "" Then Target.Offset(, 2) = Date
            '   Clear the cell in Column C if the same row in Column A is cleared
            If Target = "" Then Target.Offset(, 2) = ""
End Sub

Both don't work, which suggests that there is a problem other than a problem with the coding (also both codes work if used in a seperate worksheet). Where would I run the:

Sub foo()
  Application.EnableEvents = True
End Sub

Straight after the rest of the coding, or should I clear the other coding, and then run it? Thanks for the help so far!

EDIT: Added Code Tags - Smitty

 

[erik.van.geit]

the only purpose of my little code is to "reactivate" the triggering of events
which can also be achieved by closing and reopening Excel
you can run it in a normal module

the line

MsgBox "changecode running"

is testing wether the code is running

 

[Datsmart]

Chris,

Your code works fine for me. (First one posted).
Did you put it in the worksheet module?
Right click the sheet tab, choose view code.
Paste the code in the panel that opens and close VBA editor.
Now that sheet should add the date and time to column B when something is input to column A.

 

[Chris Martin]

I did just that, and it worked fine. Then after having done some editting it somehow doesn't work. So is there anything that I could haven done to stop the script (I don't know if thats correct language to use in VB!) working? If so, then what might that have been, and how could I make it work again?

OK, well I just tried copying the sheet into a new workbook, and it now works. So I don't know what I inadvertantly did, but it seems to work now! Thanks for the help anyway!