Event Macro help

I would like to record and display the date in a column when a particular 
event happens. 

For example, I'd like to display the date that each row in Column A displays 
"Complete."

If the task in A1 is completed today, I'd like B1 to display "3/22/2010" and 
for it to stay that way unless changed or deleted.

Thanks!
0
Utf
3/22/2010 6:11:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
850 Views

Similar Articles

[PageSpeed] 32

'Note that for this to work, column A must be manually changed,
'not a formula creating an output.

Private Sub Worksheet_Change(ByVal Target As Range)
'Is it a cell we care about?
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
'Is it a single cell?
If Target.Count > 1 Then Exit Sub
'Is criteria met?
If UCase(Target.Value) = "COMPLETE" Then
Target.Offset(0, 1).Value = Date
End If
End Sub

-- 
Best Regards,

Luke M
"Scott" <Scott@discussions.microsoft.com> wrote in message 
news:ADFCBE42-A7DB-41B9-ACBC-993FFA02CA9D@microsoft.com...
>I would like to record and display the date in a column when a particular
> event happens.
>
> For example, I'd like to display the date that each row in Column A 
> displays
> "Complete."
>
> If the task in A1 is completed today, I'd like B1 to display "3/22/2010" 
> and
> for it to stay that way unless changed or deleted.
>
> Thanks! 


0
Luke
3/22/2010 6:16:42 PM
Scott,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        If UCase(Target.Value) = "COMPLETE" Then
        Target.Offset(, 1).Value = Date
        End If
    End If
Application.EnableEvents = True
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Scott" wrote:

> I would like to record and display the date in a column when a particular 
> event happens. 
> 
> For example, I'd like to display the date that each row in Column A displays 
> "Complete."
> 
> If the task in A1 is completed today, I'd like B1 to display "3/22/2010" and 
> for it to stay that way unless changed or deleted.
> 
> Thanks!
0
Utf
3/22/2010 6:53:01 PM
What happens when the original entry is changed and no longer matches the 
"Complete" status?  I would want for B1 to clear.  

"Scott" wrote:

> I would like to record and display the date in a column when a particular 
> event happens. 
> 
> For example, I'd like to display the date that each row in Column A displays 
> "Complete."
> 
> If the task in A1 is completed today, I'd like B1 to display "3/22/2010" and 
> for it to stay that way unless changed or deleted.
> 
> Thanks!
0
Utf
3/22/2010 7:19:01 PM
Reply:

Similar Artilces:

Need help setting up Front-End/Back End system with RPC/HTTP!
This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C46459.171D7550 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hopefully someone can give me a quick answer to this problem. Previously I had setup a Ex2003 server with RPC/HTTP and everything = worked great. Just today we moved to a front end/back end setup and = I've moved all the SSL certs and setup RPC front end back end settings = on all servers. The problem is that clients using RPC/HTTP can connect = to the server just fine while inside the LAN but whe...

macros #32
I have a macro that when I run it it does not seem to filter. This same macro in another file works like a charm what could be wrong? Someone please help me - I am pressed for time. Hi Elvis without seeing the macro its impossible to come up with an accurate solution - but one idea that springs to mind is that the code is referring to the wrong sheet or range .... but it seriously would help if you pasted the code & gave us an idea of what the sheet was called, what range the data covered & what the column headings are called. Cheers JulieD "Elvis" <Elvis@discu...

Printing Private events/appts. on Calendars
A while ago I asked about how to stop Outlook printing private appointments or events in the calendars. Someone said to check "don't print private events" in the print screen which I did. Anyway, the calendar that gets printed, in the day that there is a private event it says "Private Event". How do I stop it from printing them altogether? I want it to appear on the calendar as nothing. ie. if there is only that event then the calendar should appear with no events for that day. Thanks ...

OnLoad event criteria
If have the code below in the OnLoad event which is working fine for the most part. The second line is causing a problem for cases where there is no matching record. The code is setting the value - I'd like to have it check the value first, and if it matches the value for the related field in the current form recordset, set the value, otherwise just open the form without setting the value. If CurrentProject.AllForms("AllOpen").IsLoaded Then Me.Combo1 = Forms!AllOpen!ID ' Find the record that matches the control. Dim rs As Object Set rs ...

Error in Event Viewer event id 1023 pop3 connector, 1029, 1036, 10
Hello Exchange Administrators, I am getting these Error in Event Viewer "event id 1023 pop3 connector, 1029, 1036, 1015, 1019, 1015" on regular basis after every 15 min for every user. I need yours help to fix these errors. Also tell me why these error are logged in event viewer or cause of these errors We are using WIndoes 2003 SBS, Exchange Server 2003. Thanks you for yours help in advance. On Fri, 1 Sep 2006 11:11:01 -0700, Guri <Guri@discussions.microsoft.com> wrote: >Hello Exchange Administrators, > >I am getting these Error in Event Viewer "event id 1...

Please help! How do you increase an expense in your budget without affecting the past reports?
OK imagine I have a budget in which I've been spending 100$ per month on movies. Last month I spent $120 on movies so I was over my budget by $20. Now I decide "I think I will up my spending on movies to $140 per month." I make the change in my budget, now when I look back at how I've done it calculates at $140 the whole way back through my money file and so it looks like I actually saved $20! This is NOT what happened. I want to increase the expense from a certain date not apply it retropsectively to the whole budget! How do you stop this from happening????? Paul...

deleting events in calender
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Email Client: pop I have inadvertently imported a weekly event into my calender. Each event seems to be logged as an individual event rather than a repeat event. I do not know how to delete it. Am trying, unsuccessfully, to show all events in one window so that I can easily select this irrelevant event, but cant display that window. Can you help me, either showing that elusive window, or how to otherwise delete MANY events. In article <59bb30c6.-1@webcrossing.JaKIaxP2ac0>, <leaest@officeformac.com> w...

macro is very slow . . .
Hi to All, I made the following very simple macro saved it in Personal.xls it works, but it runs *very* slowly Any suggestions to speed it up? Thank you ============================ Sub InsertBlankRows() ' ' InsertBlankRows Macro ' Macro recorded 13 12 2007 by Consultant ' ' For nn = 1 To 10 Selection.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next nn End Sub =============================== PS: "very slow" = about 6 seconds for each loop! my machine has 2GB of RAM and runs many other, much more complicated macros a lot faster . . . On Dec 1...

PLEASE HELP ME with page numbering
I posted this earlier, waited all day for a response only to be told that I was on a Mac bulletin board that I was led to with a bad interface???? anyway here is my question. I am DESPERATE my page numbers are not showing in my document and I am at the end of my > rope. I am using MS word 2007 and Vista - I am able to go through all the > menus for header, footer, format page numbers, etc. but then no final product > shows up. > > The help desk at my school is clueless and offered no help whatsoever. > > I have spent HOURS trying to insert these page numbers today. I ha...

4117 Event ID Error
Starting 1-29-04 we started getting 4117 error messages just like those discussed in Micrisoft's Knowledge Base Article 190084. The only thing that arcticle suggests is to update to SP 4 which is already installed on the machine. I have never seen this message before and now we are getting one every few minutes. Any help would be appreciated. Thanks Sean Seems like you have something else going on then, check out the following articles and see if these help: 126497 XADM: Limiting the Number of Recipients of a Message http://support.microsoft.com/?id=126497 230250 XFOR: Internet ...

Catch click event button
Hi all, I was wondering... Is it possible to "catch" a click-event on a BuildIn Offie Button? In order to execute your own VBA-code? e.g. I want to be able to catch the "Undo" button in order to execute a piece of macro code in stead of just "Undoing" only the last action. Greetz, Bert -- Neron It is possible to catch ("intercept") most built-in Word commands, as described at http://www.word.mvps.org/FAQs/MacrosVBA/InterceptSavePrint.htm. In this case, the macro you need to write is named EditUndo, as in Public Sub E...

Odd e-mail duplication problem
All e-mails promoted or Tracked in CRM Outlook client are being copied to one particular Account that is unrelated. The e-mails are copied correctly to the history of the desired accounts and Contacts but also copy to one particular Accountl as well. As anyone seen anything like this and have any ideas what could be wrong? -- Darryl - dh@mtccrm.com -www.mtccrm.com - Only Microsoft CRM ...

Event After the sale
Hi to all Is any way to bypass the event that after you complete the sale you get a window with the change in it, in order to go to the next sale you have either hit OK or enter because if you scan an item from that point it does not scan in and it appears to me that you can miss an item from been scanned in any way to fix this issue. Also if you have scan several items into the system and you go to del one item in the middle of the page RMS is loosing its focus and starts to scan into the next line down which deletes the items that have been scanned.I think it should go to the next ...

Flexible X axis VBA/Macro generated
I am trying to create a chart showing a payment profile with a fixed start date but variable end. Manually, simple, select all cells with a date before the end date and that is X axis, and series data for appropriate cells. How can I create an X axis range which only goes to the end year? The same process will presumably apply to series data also. This cannot be so difficult I would have thought. Am I missing something? Duncan Beech -- duncan beech Skip the macros, and try this approach: http://peltiertech.com/WordPress/dynamic-charts/ At the end of the tutorial are links to speci...

When running a macro I keep getting a box (Print Macro Definition.
I have a macro in which I am printing a report made from a query and each time I run the macro a box comes up sayd print Macro Denifitions and I can check or uncheck properties, actions and arguments and permissions by user and group. Or I can click on Ok or cancel. If I click on cancel, it runs ok and doesn't print out anything, but if I click on OK, then about 10 pages come out aft er the report prints. ...

Macro warning message
Anyone else had this problem. We installed Excel97 on an NT Server.We then installed Excel2000 over it, and set the macros security setting to low. We re using NetOp to connect to that server, and when we open an Excel workbook with macros, no problems. However, if the screensaver kicks in, and a workbook is opened by a scheduled job, we get a dialog box regarding the macros. If we are connected, we can respond, but as we run 300 Excel queries, and as we won't be connected to the server most of the time, this is a problem. Anyone else experienced it, and know the solution? Bob ...

Event
I have recorded a macro that I need to automatically run at 6:45am daily. How do I specify this in the code? Everything you need to know about OnTime procedures (but were afraid to ask): http://www.cpearson.com/excel/ontime.htm You'll need to have excel running for this to work. ===== Some versions of windows have scheduler programs. (Or you can get a freeware/shareware version on the web (www.shareware.com is where I look first.) Then you could have that scheduling program start an excel workbook. If you name the macro Auto_Open, then it'll run when the workbook opens. (But ...

list control design help
Hello again, hopefully you guys can me in the planning stage for this next project I am working on. Essentially I want to create a multi-column list control (I.e. - report mode) but it has to allow two things which a normal list control doesn't seem to allow. First I want to be able to sort the columns individually (I.e. not just the main column). Now I fixed this problem by looking at a code example off of code project so that isn't a problem anymore (1 down). The next problem I have is I want to be able to search each column (for example when you press the "b" key...

Need Help with a Sum of a Product of a Currency and Product
Does anyone have a way to simplify this formula and make it so it automatically includes newly added rows or cells? =SUM((SUM($K$9:L9)*$H$9),(SUM($K$11:L11)*$H$11),(SUM($K$13:L13)*$H$13), (SUM($K$15:L15)*$H$15),(SUM($K$17:L17)*$H$17),(SUM($K$19:L19)*$H$19), (SUM($K$21:L21)*$H$21),(SUM($K$23:L23)*$H$23),(SUM($K$25:L25)*$H$25), (SUM($K$27:L27)*$H$27),(SUM($K$29:L29)*$H$29),(SUM($K$31:L31)*$H$31), (SUM($K$33:L33)*$H$33),(SUM($K$35:L35)*$H$35),(SUM($K$37:L37)*$H$37), (SUM($K$39:L39)*$H$39),(SUM($K$41:L41)*$H$41),(SUM($K$43:L43)*$H$43), (SUM($K$45:L45)*$H$45),(SUM($K$47:L47)*$H$47),(SUM($K$49:L49)...

Help with Formulas #2
I need help with creating a formula. I want to count the total number of individuals who meet a certain criteria in one column and a certain criteria in another column. For example: I want to find out the total number of people who live in Germany and went on a specific trip. For argument sake let's say column A is at lists only Y or N for yes and no, and column B is a list of Countries such as Germany, America, etc. I would like to figure out the total number of individuals who have a Y in column A and the word Germany in column B. This is what I have done so far and it works for co...

Help Everything is gone. It's all been deleted.
I went on excel last on Dec 5th and I saved before exiting as usual. When I opened the file today everything was gone. There is nothing there. The file it's saved as is still there but it's a new page. I don't know what to do. I need that information. HELP! Are you sure you opened the same workbook? What do you mean by "page"? Excel has pages only when you print. Do you mean a blank worksheet? Are there any other worksheets available or just the one blank one? Did you create a backup of the workbook when you saved? If you "need" the inf...

Recurring event
Is there a way to set a meeting date and have it recurring each month, but not on a certain week or certain date of each month. My meeting happens on a Thursday in each month but not every 2nd, 3rd or 4th Thursday. Some months it is the 3rd Thursday and some it is the 4th Thursday. ...

Macro does not run when data refreshed
I set up a worksheet at work that queries our lab information system for incomplete high priority orders. The results of the query are display in order by age, oldest to newest. The Data Range Properties is set to refresh the data every 2 minutes and overwrite existing cells with new data and clear unused cells. After new data is imported there is a Visual Basic macro that compares the time of each order to the current PC time and changes the color of the text from green to yellow to red as the order ages. All of the works great. I've recently duplicated this worksheet at a secon...

Please help with finding correct functions!!!
I am trying to configure Excel to balance my checkbook. Column A is Trans. Type, Column B is Date, Column C is Description of Trans., Column D is Debits, Column E lists if items are posted, Column F is Debits and Column G is Balance. I'm trying to get Column D (debits) to subtract from Column G (balance) and get a new balance (new column? H?) and get Column F (credits) to add to Column G (balance to get a new balance (again, new column? H?). I am not computer illiterate and I should be able to figure this out by myself. But I've been working on finding the right functions (l...

What event when I click the record selector
I have some labels on the form would like to populate its value (they are calculated values) when I select the record selector, next, previous or last or first. What event should I write the code into? On Tue, 22 May 2007 11:01:23 +1000, "Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote: >I have some labels on the form would like to populate its value (they are >calculated values) when I select the record selector, next, previous or last >or first. > >What event should I write the code into? > Well, don't use Labels for this purpose; use textboxes ...