Run a macro when a cell is updated
 

Hi all,

I'm hoping someone can help get me out of a pickle before by brain
melts.

I am using the following macro in my worksheet -

worksheets(\"stats\").activate
activecell.offset(rowoffset:=0, columnoffset:=1).activate
selection.copy
selection.pastespecial paste:=xlvalues, operation:=xlnone,
skipblanks:= _
false, transpose:=false

I am trying to figure out how to launch this macro, when a cell is
updated. (If this is possible)

Basically, I have a stats spreadsheet for tracking work, and need to
capture the time a piece of work is completed.

Column B will record this by using this formula-

=IF(A1="y",NOW(),"Outstanding")

Then, when cell A1 is updated with a 'Y' to indicate the work is
complete, the current time is recorded in B1. Then by running the
macro, the time is fixed and will not change (as the NOW() function
will continually update).

If I can get the macro to run when A1 is updated, my problem is
solved.

I have considered using a checkbox or button to run the macro. There
are a couple of problems with this. Firstly, as the worksheet may
contain thousands of rows it is time consuming to duplicate the object
in each row. Secondly, when the object is selected/clicked - there is
no guarantee that A1 will be the activecell - meaning the macro will
not do it's intended job.

Could also use shapes - which can be linked to the corresponding cell
to work around this potential problem. However, same issue - with
thousands of rows - I will have to update each shapes cell link.

Any help would be greatfully accepted!!!!

Cheers

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=490997

Run a macro when a cell is updated
 

In the sheet that you will contain those cells, add

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = Format(Now, "dd m yyyy hh:mm:ss")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


Change the range A1:A10 to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"systemx" wrote in
message ...

Hi all,

I'm hoping someone can help get me out of a pickle before by brain
melts.

I am using the following macro in my worksheet -

worksheets(\"stats\").activate
activecell.offset(rowoffset:=0, columnoffset:=1).activate
selection.copy
selection.pastespecial paste:=xlvalues, operation:=xlnone,
skipblanks:= _
false, transpose:=false

I am trying to figure out how to launch this macro, when a cell is
updated. (If this is possible)

Basically, I have a stats spreadsheet for tracking work, and need to
capture the time a piece of work is completed.

Column B will record this by using this formula-

=IF(A1="y",NOW(),"Outstanding")

Then, when cell A1 is updated with a 'Y' to indicate the work is
complete, the current time is recorded in B1. Then by running the
macro, the time is fixed and will not change (as the NOW() function
will continually update).

If I can get the macro to run when A1 is updated, my problem is
solved.

I have considered using a checkbox or button to run the macro. There
are a couple of problems with this. Firstly, as the worksheet may
contain thousands of rows it is time consuming to duplicate the object
in each row. Secondly, when the object is selected/clicked - there is
no guarantee that A1 will be the activecell - meaning the macro will
not do it's intended job.

Could also use shapes - which can be linked to the corresponding cell
to work around this potential problem. However, same issue - with
thousands of rows - I will have to update each shapes cell link.

Any help would be greatfully accepted!!!!

Cheers

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile:
http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=490997