Automatically Insert Date and Time

Chris Martin

New Member
Joined
Oct 2, 2006
Messages
3
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
Sub foo()
Application.EnableEvents = True
End Sub
does it work now ?

you might add a testline onto start the procedure
Code:
MsgBox "changecode running"
there should be a popup when you change something to the sheet

kind regards,
Erik
 

Chris Martin

New Member
Joined
Oct 2, 2006
Messages
3
The code I am using is:

Code:
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:

Code:
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:

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
Code:
MsgBox "changecode running"
is testing wether the code is running :biggrin:
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Oct 2, 2006
Messages
3
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!
 

Forum statistics

Threads
1,136,430
Messages
5,675,803
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top