Date / time stamp in Excel 2

[Sam577]

Hi,

I have a spreadsheet that I use to forecast my workload, which requires users to fill in their requirements.

I am trying to add a date/time stamp to prevent the users from cheating by pretending they booked before they did.

I tried the following but it doesn't work (nothing appeared in column N when I made an entry in column G and saved).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("G:G").Column Then
Cells(.Row, "N").Value = Now()
End If
End With
Next Cell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


Does anybody know what I did wrong (bearing in mind my VB is elementary and that my existing knowledge is based on FAQ707-1657)? I followed FAQ707-1657, SkipVought's FAQ, How to Add a DateStamp in Excel, exactly. The only thing that confused me was point 2: it says "right click the sheet object you want the date stamp on" -- is there something missing here -- was I supposed to choose one of the resulting menus after right clicking?

Also, I've noticed that even though I selected Change in the procedure drop down box (and Worksheet in the object drop down menu), the procedure box now says SelectionChange).

Many thanks
Sam

 

[AnotherHiggins]

In the VBEditor, is your code in a module or in the worksheet?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    dim rng as range
    Dim r As Range  'CELL is not a good practice
    set rng = application.intersect(Target, range("G:G"))
    if not rng is nothing then
 
      application.enableevents=false

      For Each r In rng
           With r
               Cells(.Row, "N").Value = Now()
           End With
       Next Cell

       application.enableevents=true

    end if
End Sub

Skip,

_[red][/red]

 

[Sam577]

I'm pretty sure it's in the Worksheet. I've selected the relevant sheet in the project explorer.

 

[Sam577]

I tried Skip's code but it stopped at the word Cell, as below, with the error message 'Complile error: invalid Next control variable reference'

End With
Next Cell

application.enableevents=true

 

[SkipVought]

delete the word CELL

Skip,

_[red][/red]

 

[AnotherHiggins]

replace it with r as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    dim rng as range
    Dim r As Range  'CELL is not a good practice
    set rng = application.intersect(Target, range("G:G"))
    if not rng is nothing then
 
      application.enableevents=false

      For Each r In rng
           With r
               Cells(.Row, "N").Value = Now()
           End With
       Next [red]r[/red]

       application.enableevents=true

    end if
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Sam577]

Thank you all -- I have my date stamp. Now let those scoundrels try and trick me -- thanks so much!

 

[Sam577]

Hi again,
The date stamp is working well but I want to lock the column where the date stamp appears to prevent users from amending the time. However, after locking the relevant cells and choosing Tools>Protection>Protect Sheet in Excel, and protecting the sheet with a password, the macro stops working. Is there a way to lock / protect the date stamp values without ruining the macro?
Many thanks,
Sam

 

[PHV]

Have a look at the Unprotect method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bong]

You could run your macro inside Sub Auto_Close. Of course, you would need to capture the current state in Sub Autpen and figure out what has been added.

_________________
Bob Rashkin