Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to enable macro and updated link without confirmation? | Excel Discussion (Misc queries) | |||
Excel cells randomly don't get updated unless each cell is updated | Excel Discussion (Misc queries) | |||
Run Macro(save workbook) after cell updated | New Users to Excel | |||
Macro not running when cell value is updated | Excel Programming | |||
Macro to Keep Linked Workbooks Updated | Excel Programming |