It's me again, Keith. Do you want this to execute in every worksheet of workbook, or only in a particular worksheet? I ask because for both solutions you'll write approximately the same program, but if it's to operate on only a particular worksheet then you'll store it in the "Excel Object" that corresponds to that worksheet, but if in every worksheet then you'll put it in the ThisWorkbook object. You can see those in your VBA editor under "Microsoft Excel Objects" rather than "Modules".
(And by the way I'm assuming that you already know something about the VBA editor. If you don't—if this'll be your first macro—I should back up and start at the beginning.)
The macro you write will be named "Worksheet_Change"—it must have that exact name, because that way you cause it to respond to an event with that exact name—and it has to have the prescribed list of arguments with exactly the right data types, though each argument can be
named what you choose. All this is defined
here; take a look. Within those limitations your macro can do whatever you like.
I have a short Worksheet_Change macro that looks like this:
Code:
Private Sub Worksheet_Change(ByVal Tgt As Excel.Range)
If Cells(1, Tgt.Column).Value <> "From" Then Exit Sub
Set co = Cells(Tgt.Row, 1)
' If there's already a date there, it'd better be today's.
vd = co.Value
If Not IsEmpty(vd) Then
If vd <> Date Then Abend "Not today's date."
End If
' Otherwise make it so.
co.Value = Date
End Sub
This goes in a timesheet I maintain. Whenever I enter a value in one of the columns headed "From", it put's today's date in the date column. If it already had today's date in that cell, fine; but if it had some other date (in case I made a mistake) it displays a warning message. That's all it does. Maybe you can use it as a sort of template.
10-28-2014, 07:34 AM
Similar Threads
VBA code to select one word from a text in a cell and place that word in next cell
Windows 7 64bit 
Office 2010 32bit
Hybrid Mode