How do I change the value in cell based on a future date

I would like certain cells to be cleared after a certain date
0
W (121)
12/20/2004 11:43:08 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
196 Views

Similar Articles

[PageSpeed] 11

one way:

Put this in the ThisWorkbook code module (Right-click the workbook title 
bar and choose View Code):

    Public Sub Workbook_Open()
        If Date > #12/15/2004# Then _
            Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents
    End Sub

Note that this won't work if the user opens your workbook with macros 
disabled.

Note also that there's no way to prevent a user from accessing your data 
- i.e., they can set their clock back and open the file. You can make 
things more difficult, but not much...


In article <FF81B36F-D5E8-4599-9B19-9E47A9A9FC84@microsoft.com>,
 John W <John W@discussions.microsoft.com> wrote:

> I would like certain cells to be cleared after a certain date
0
jemcgimpsey (6723)
12/21/2004 12:51:09 AM
And just to add to JE's post...

If the user allows this macro to run, then those cells will be cleared each time
the workbook opens.



JE McGimpsey wrote:
> 
> one way:
> 
> Put this in the ThisWorkbook code module (Right-click the workbook title
> bar and choose View Code):
> 
>     Public Sub Workbook_Open()
>         If Date > #12/15/2004# Then _
>             Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents
>     End Sub
> 
> Note that this won't work if the user opens your workbook with macros
> disabled.
> 
> Note also that there's no way to prevent a user from accessing your data
> - i.e., they can set their clock back and open the file. You can make
> things more difficult, but not much...
> 
> In article <FF81B36F-D5E8-4599-9B19-9E47A9A9FC84@microsoft.com>,
>  John W <John W@discussions.microsoft.com> wrote:
> 
> > I would like certain cells to be cleared after a certain date

-- 

Dave Peterson
0
ec357201 (5290)
12/21/2004 1:27:15 AM
Reply:

Similar Artilces:

Running sum by contract nbr and Date
I have a table with the following fields: Contract_nbr Draw_date Draw_Amt What I'd like is to have a running sum of the Draw_Amt by Date for each contract_nbr. In other words I don't want the running sum to be cumulative for the entire table but start afresh with each new contract nbr. I've tried using Dsum, but I haven't had any luck. basically I'm trying to find a point in time when a contract reached an amount drawn. Any help would be appreciated! Any help would be appreciated. One approach might be to use a query to return the values, but use a report ba...

Changing Radians to Degrees
Hi, I am using Excell 2007, and I prefer to use degrees rather than radians. When I calculate Sin (angle), excel identifies the angle as in Radians. How can I make "degrees" to be my default unit of angles? Leo On Sun, 15 Nov 2009 12:34:13 +0300, "Leonard" <akwilapo@chem.udsm.ac.tz> wrote: >Hi, >I am using Excell 2007, and I prefer to use degrees rather than radians. >When I calculate Sin (angle), excel identifies the angle as in Radians. How >can I make "degrees" to be my default unit of angles? >Leo As far as I know, ...

Trendline values #2
I used the function "LOGEST" to get an exponential fit of a series of numbers, which gives me a value of 1.0610. I also used "RSQ" function to get the R-square, which is 0.9442. Then I made a chart based on the same series of numbers and add trendline to the curve. I selected "Exponential" under "Type" and checked "Display equation on chart" and "Display R-Squared value on chart". The value shown on the chart is "y=3928.8e^0.0592x" and "R^2=0.9104". I expected to see "y=3928.8e^0.0610x" and "R^...

how do you make a line of a chart change color halfway through?
How could you change the color of a line in a line chart so that at some point on the graph the line changes color? Say like the line chart shows data over the months of a year but at month July, the line on the chart changes from blue to green and is green the rest of the months. I am asking about 1 line being two colors. Hi, You could format each segment of the line. Select the series and then use the Right cursor to move through the segments. Note if you only have 1 series changing a section of the series will cause the legend to report each segment. The other way is to use multip...

No date fields appear when exporting/importing CSV file?
Hello All, I need to convert some legacy emails from another system to a format that can be read by Outlook. The preferred format is CSV text, which we plan to import into Outlook. I noticed when playing with the export/import that the following seem to be the standard fields that get exported into an Outlook generated CSV file: "Subject", "Body (text)", "From: (Name)", "From: (Address)", "From: (Type)", "To:(Name)", "To: (Address)", "To: (Type)", "CC: (Name)", "CC: (Address)", "CC: (...

Counting occurences of codes in cells
I have a spreadsheet with various codes separated by periods, in a single cell. Like: C T C,R T,C,R R and so forth. It's easy to count the single codes but how do I cound the "C" when it's with another character? There must be a simple way, without writing code, to do this. Is there? Thanks! maybe... =countif(a1:a10,"*" & "c" & "*") or =countif(a1:a10,"*c*") or if x99 contained the letter c. =countif(a1:a10,"*" & x99 & "*") salgud wrote: > > I have a spreadsheet with various codes separa...

problem carrying over values
I have one form that has a button I''ll call it form1. The button looks at a text box for an ID and if its null it opens form2 and creates a new record in form2. If the text box is NOT null, it will go find the ID that's in the text box and filter it in form2. When the new record is created in form2, I need the ID to be put into the NULL text box in form1. I know how to do this however, if you open form2 on its own, then the error msg comes up that it can't find form1. I know why I'm getting this error, but don't know the fix for it. Is there a work around? Thanks...

Change Folder List Text Color (2002)
Is there anyway to change the color of the text listed in the folder view? ie - "Inbox" text color from black to red, "Sent" from black to blue" No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, jsonne@verizon.net asked: | Is there anyway to change the color of the text listed in | the folder view? ie - "Inbox" text color from b...

Values in the Legend?
Is there a way to show values in the legend - behind the series text? Thanks, Phil Do you want to show just a single value? You can either build a lengthier label (like =A1&B1), and use this as the new series name, or you can select multiple cells for the name, and Excel puts them together with a space between. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Phil Hageman wrote: > Is there a way to show values in the legend - behind the series text? > Thanks, Phil ...

Is this error exchange 2003 based?
HI, I have a ecommerce package that after I submit the form, it usually sends an email, I recenlty moved this site to a new server with Exchange and now when I try and submit, I get this. Exchange 2003 and the website are both on the same W2k3 server. error '8004020f' /ssl/SFLib/mail.asp, line 311 Thanks David Also, In IIS would the application Pool be ExchangeAppPool or the DefaultAppPool being that this program uses CDOSYS. Thanks much "David" <dross@si.rr.com> wrote in message news:#6m611lsEHA.1344@TK2MSFTNGP10.phx.gbl... > HI, I have a ecommerce package ...

How to change Menu font in Outlook 2007?
Hi I hate new ClearType font in outlook2007, i can turn off Cleartype and change mail font, but cant change Menu font, now it use Segoe or Calibri without ClearType and it look awful. Thx No, it always uses Segoe UI. Patrick Schmid [OneNote MVP] -------------- http://pschmid.net *** Office 2007 RTM Issues: http://pschmid.net/blog/2006/11/13/80 Office 2007 Beta 2 Technical Refresh (B2TR): http://pschmid.net/blog/2006/09/18/43 *** Customize Office 2007: http://pschmid.net/office2007/customize RibbonCustomizer Add-In: http://pschmid.net/office2007/ribboncustomizer OneNote 2007: http://pschm...

Taks filtering based on category and working hours?
I am using Outlook 2007 and I would like to filter the tasks shown in the "Outlook Today" Tasks column as well as in the Calendar view's "To-Do Bar" based on categories (like e.g. "Business" or "Private") and based on whether we are within working hours (as configured under Tools => Options => Calendar options => Calendar work week) or not. I.e. during my free time I don't want to see any tasks categorized as "Business" and during working hours the ones categorized e.g. "Private" should be hidden. Is that so...

create chart /table excel-save, close & reopen colors change? Why
When I create a chart/table in Excel - save, close & reopen to use again, the colors have changed for my formating. How do I set the formating so that the colors stay the same - I have tried styles with no luck. The colors need to be the company approved. Thank you, Anne, I am unable to reproduce yr problem. Excel should NOT override yr manual settings. However,you can set yr company colours as default under Tools/Options/[Color tab]. Here are the colours Excel uses by default for the workbook. (Under that thin line you see the defaults for fill and line colours.) Hope this...

qry for dates in 1st 2nd 3rd and forth quarters of current year!
Hello i am looking to write a query which returns values for the current year only! DatePart("q",[Start Date],Year([start date])=Year(Date())) is what i have written but this includes enteries for next year which i dont need! any ideas please!!! thanks in advance hi, smason wrote: > DatePart("q",[Start Date],Year([start date])=Year(Date())) > is what i have written but this includes enteries for next year which i > dont need! any ideas please!!! You need to filter for the current year, e.g. SELECT DatePart("q",[Start Date]) FROM [yourT...

Conditional Cell Category
Is it possible to make a cell's category conditional on the value in another cell? I would like to have a cell display its value as a currency if the cell before it says "Monthly", and as a percentage if the cell before it says "Annual". I am working in Excel 2003. Thank you, Trey ~ On 29 June, 15:39, one third <g...@hotmail.com> wrote: > Is it possible to make a cell's category conditional on the value in > another cell? > > I would like to have a cell display its value as a currency if the > cell before it says "Monthly", and as ...

Change Status Workflow
Is the Change Status workflow rule treated like a Manual workflow in that you have to manually apply the rule - or will it fire autmatically? I am creating a very simple WF but it seems to only work if I apply it manually. Here is my WF: When an Account is created and the AccountRatingCode is set to a certain value [in this case "Premier"] or when the AccountRatingCode is set to "Premier" for an exisitng Account then create a task for the account owner to call that Premier account. Also, will workflows fire on custom schema fields? Change Status only applies to ch...

How to change page number font/color
I have inserted page numbers into a 30-page document using the Insert command. How may I change the font size and color of the numbers. Ideally I'd like to create a number with a line above it. cuig wrote: > I have inserted page numbers into a 30-page document using the Insert > command. How may I change the font size and color of the numbers. Ideally > I'd like to create a number with a line above it. You can change the format of your page numbers just like the format of any other text. Ctrl+M to get to your master page, select the number, then change the font...

How to change cell data from all upper to title case
I need to change several columns of data from all upper case to Title Case. What formula should I use & How can I apply the formula to the data in each cell? Check out the Proper() function. It changes any case to title case, and can be copied from cell to cell to apply the formula to your data. Check out the Proper() function. It changes any case to title case and you can copy it to a range of cells to apply it to your data. You have other answers showing the use of the PROPER Function to act upon one cell at a time. If you want to change many cells at once you will need a mac...

Changing Comments on Sales Invoices and Returns
I have posted Invoices and Returns that have incorrect comments on them. I know that the Comments are held in the SOP30200 in a field called Comment ID. In this case they did not use an established Comment ID, so it's reflected as untitled on the document and blank in the table. I'm trying to find out if someone has attempted to alter comments after the documents have posted. If I pull the historical document up in Inquiry>Sales>Sales Document>Sales Order Processing Document Inquiry>Sales Transaction Inquiry Zoom, I can see the old comment. I just don't know...

my switchboard prevents me making changes to the database
the database opens with a switchboard screen and doesn't provide access to all the database tools so I can make changes, open tables, etc. In Access 2000 and 2002 you would hold down the control or shift key when you opened the database and it turned off the switchboard control features to open normally. I'm using Office 2003. Is there a similar key to press and hold when you open a database to turn the switchboard feature off? Thanks! Holding down the shift key works exactly the same in Access 2003 as in previous versions. If it's not working for you, perhaps someone set ...

Only Display records before and after a change in teh value of a f
I have a table that looks like the following RUNID | DATETIME | PROCESS 0001 1/1/09 1201 Neck 0001 1/1/09 1202 Neck 0001 1/1/09 1203 Neck 0001 1/1/09 1204 Neck 0001 1/1/09 1205 Neck 0001 1/1/09 1206 Body 0001 1/1/09 1207 Body 0001 1/1/09 1208 Body 0001 1/1/09 1209 Body 0001 1/1/09 1210 Neck 0001 1/1/09 1211 Neck 0001 1/1/09 1212 Neck 0001 1/1/09 1213 Neck 0001 1/1/09 1214 Body 0001 1/1/09 1215 Body 0001 ...

Align Line Charts By Milestone Dates?
I'm a chart neophyte. I have a worksheet with multiple line charts, each line depicting total hours per month per project. I would like to align the lines to a common milestone, like "start of detailed design". Each project of course has a different date for this milestone. Other than knowing that somewhere I'll have to specify the dates of the milestone for each project, I have no idea of what to do. Any help/insight will be appreciated. Thanks, Mike Each data series has a column of dates and a column of percent complete values. I'll use columns A and B in...

determining macro status in a cell
Hi Is there a way to determine the status of macros (enabled or disabled within a cell formula? Thank -- Message posted from http://www.ExcelForum.com I don't believe this is possible. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "newengland >" <<newengland.11d8au@excelforum-nospam.com> wrote in message news:newengland.11d8au@excelforum-nospam.com... > Hi > > Is there a way to determine the status of macros (enabled or disabled) > within a cell formula? > > > Thanks > > > --...

line spacing in cell
I would like to type 3 pieces of text, each on a seperate line in one cell. I've looked under cell format Press Alt-Enter where you want to start a new line! Regards, Stefi „dlh” ezt írta: > I would like to type 3 pieces of text, each on a seperate line in one cell. > I've looked under cell format Thanks so much. Works great!! "Stefi" wrote: > Press Alt-Enter where you want to start a new line! > > Regards, > Stefi > > „dlh” ezt írta: > > > I would like to type 3 pieces of text, each on a seperate line in one cell. &...

Adding an Active X control to a cell
I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it fits over the top of the cell seems to be a rather inaccurate method of doing it. Hold down alt while you place it in the cell and it will fit the cell -- Regards, Peo Sjoblom "Rick" <rstenson@qualnetics.com> wrote in message news:317AA5B4-7970-45B2-8497-556670E48018@microsoft.com... > I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it f...