Event procedure?

  • Thread starter Thread starter Butch
  • Start date Start date
B

Butch

Can event procedures be run in Excel as they can in Access? Specifically, I would like a cell containing a date
to change to "Now" if data in another cell is changed. Is that do-able? If so, how?
 
Hi Butch

yes this is do-able via a worksheet_change macro,

e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A2").Value = Now
End If
End Sub
----
This says if A1 is edited then put the date & time into A2

to use this code, right mouse click on the sheet tab of the sheet you want
to run the code against and choose view code. Copy and paste it directly
into the right hand side of the screen. If any lines go red, click at the
end of the line and press delete - this should fix any line wrap problems.

Hope this helps
Cheers
JulieD
 
Thanks for the advice, but something else is amiss.

The code worked well, except I can't seem to save it. When the program is closed out it loses the code. I have clicked
on the Workbook "Save" button while the Visual Basic window is open and then again before closing out the Workbook -
but the code is not there when I re-open it. Am I overlooking another "Save" button somewhere?

Also, the Visual Basic window has the options of "Change" and "SelectionChange". Am I using the wrong one, perhaps?

--
Thanks.

- Butch, Pennsylvania
 
It sounds like you want the worksheet_change event (since you're looking at a
change for a single cell).

When you save the workbook, are you saving it as a normal "microsoft Excel
Workbook (*.xls)"?

(You can verify it if you do File|SaveAs and look at/change the "Save as type"
box.)
 
Closing in on the problem.

So far so good. Works OK when the target cell is a direct edit. But sometimes I want to paste a value into the cell
and then it doesn't respond. Is there a solution to this?

Thanks again.
 
That event still fires for me when I paste something into A1.

But JulieD's code looks for a change to only A1--if you're doing more than one
cell, then maybe that's the problem.

If you want to just check to see if A1 (among other cells changed):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Me.Range("a1"), Target) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Me.Range("A2").Value = Now

errHandler:
Application.EnableEvents = True
End Sub
 
Back
Top