BeforeClose Event does not work 1

[zevw]

I would like that when any changes are made to the sheet, the date in cell P4 should change to todays date and stay that way (NOT the NOW() FUNCTION) and close.

I put this code into the sheet, placed a breakpoint in this event and when I close the sheet it does not trigger this event. what am I doing wrong or where should I place the code that it it should be triggered (NOTE: THe work consists of one worksheet.)

Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Range("P4").Select
    ActiveCell.FormulaR1C1 = Date

End Sub

Thanks in advance for your help!!

 

[TonyJollans]

I put this code into the sheet ...

If you literally did this then the code is in the wrong place. Workbook Event code needs to go in the ThisWorkbook code module, not a Sheet module.

When you get it to run it will put the date on the sheet whether it has been updated or not; is that what you really want?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[ThomasLafferty]

Hi!
Try this:

Public Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveSheet.Range("P4").Value = Date
End Sub

Just make sure you put the routine under the This Workbook section in your project explorer.

Tom

Born once die twice; born twice die once.

 

[zevw]

ThomasLafferty

Well my mistake was I did not set it up as a public even.

I tried my code in the sheet/workbook/module objects and none of the worked. Now I added Public Sub it works like a snap.

Thanks;

 

[ThomasLafferty]

You're welcome, and thanks for the star! Incidentally, if you want this to run only if changes in the book have been made, then this isn't what you want to do. Tony is correct: this will put the date in the cell regardless of whether or not changes have been made. It sounds like you want to capture document properties like those found under File>Properties. If that's the case, why not enable track changes? In any case, do look into File>Properties because you may get a different approach that you will want to take.

Tom

Born once die twice; born twice die once.

 

[zevw]

Tony is 100% right I don't want this to happen if no changes are made. What event should I use then.

Thanks again for both of you!!

 

[ThomasLafferty]

Try this instead:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
        ByVal Source As Range)
     'runs when a sheet is changed
    If Source.Address <> "P4" Then
        ActiveSheet.Range("P4").Value = Date
        Exit Sub
    End If
End Sub

Incidentally, this still belongs under the This workbook section.

Tom

Born once die twice; born twice die once.

 

[KenPuls]

If you want it to log the time on any sheet that is changed, (every time), and on every sheet it would go in P4 then use this event in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Sh.Range("P4").VAlue = Date
End Sub

You could also exclude certain sheets by using a case statement, or control what cell is updated on each sheet the same way.

HTH,

Ken Puls, CMA

http://www.excelguru.ca

 

[firefytr]

I would add a check to ensure it's a worksheet and not any other sheet type also. And you could add the format function to the date to ensure it's put the way you want as well...

    If TypeName(Sh) <> "Worksheet" Then Exit Sub
    Sh.Range("P4").Value = Format(Date, "mm-dd-yyyy")

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenPuls]

Good point, Zack. That would save it bombing on a chart sheet.

Ken Puls, CMA

http://www.excelguru.ca