Input a date using a Form Button.

Hi, 

Please help as I'm totally lost with this one! 

I'm doing a little project for work and I have come across a littl
problem with a date input that I have set up using a Form Button. No
this button once pressed enters the date just as I want, into th
correct Cell (the buttons called "Todays Date") Now I have recorded 
macro so that the button executes the =TODAY() comand - which is fin
untill the date changes!! As you can emagine I need the date to stay a
it was when first pressed as its a database I'm trying to compile.

Does this make any sense to you all??

I will be so greatefull if I can resolve this one.

Thankyou!

--
Message posted from http://www.ExcelForum.com

0
8/4/2004 6:34:10 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
264 Views

Similar Articles

[PageSpeed] 29

Hi

Sure, perfect sense and a common problem. Forget your recording, formulas
can't do that. The macro is as simple as this:

Sub DateStamp()
Sheets(1).Cells(2, 3).Value = Date
End Sub

HTH. Best wishes Harald

"Paulwilson412 >" <<Paulwilson412.1ah8mw@excelforum-nospam.com> skrev i
melding news:Paulwilson412.1ah8mw@excelforum-nospam.com...
> Hi,
>
> Please help as I'm totally lost with this one!
>
> I'm doing a little project for work and I have come across a little
> problem with a date input that I have set up using a Form Button. Now
> this button once pressed enters the date just as I want, into the
> correct Cell (the buttons called "Todays Date") Now I have recorded a
> macro so that the button executes the =TODAY() comand - which is fine
> untill the date changes!! As you can emagine I need the date to stay as
> it was when first pressed as its a database I'm trying to compile.
>
> Does this make any sense to you all??
>
> I will be so greatefull if I can resolve this one.
>
> Thankyou!!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
innocent (844)
8/4/2004 6:54:34 PM
Instead of evaluating the XL function, use VBA's Date property:

    Public Sub Button1_Click()
        With Range("A1")
            .NumberFormat = "mm/dd/yyyy"
            .Value = Date
        End With
    End Sub

Change the target cell and format as needed.



In article <Paulwilson412.1ah8mw@excelforum-nospam.com>,
 Paulwilson412 <<Paulwilson412.1ah8mw@excelforum-nospam.com>> wrote:

> Hi, 
> 
> Please help as I'm totally lost with this one! 
> 
> I'm doing a little project for work and I have come across a little
> problem with a date input that I have set up using a Form Button. Now
> this button once pressed enters the date just as I want, into the
> correct Cell (the buttons called "Todays Date") Now I have recorded a
> macro so that the button executes the =TODAY() comand - which is fine
> untill the date changes!! As you can emagine I need the date to stay as
> it was when first pressed as its a database I'm trying to compile.
> 
> Does this make any sense to you all??
> 
> I will be so greatefull if I can resolve this one.
0
jemcgimpsey (6723)
8/4/2004 7:01:31 PM
You must be entering the date into the cell by setting the cells formul
property

ACTIVECELL.FORMULAR1C1 = \"=TODAY()\


Try changing your macro code so that it uses the following instead

ACTIVECELL.VALUE = DATE





Rolli

--
Message posted from http://www.ExcelForum.com

0
8/4/2004 7:02:42 PM
Thank you all so much!! I didnt realise you could do it so many ways.

Its works a treat!

On the down side its given me another problem....sorry to trouble yo
all again but....

now that I have entered the "TOADYS DATE" correctly I have a sligh
problem with the same date, and that is when I apply filter to the Dat
Card number (Spread sheet attached) so that it can be found easily
which brings it to the top, now when I press the Todays date button i
obviously enters it into the wrong Cell....!!!???!!! How do I tell th
Excel to enter the Todays Date into the Top row once filtered?
Unfiltered the top row is 14.

Thank you once again for you kind help!

oops sorry file too big 

--
Message posted from http://www.ExcelForum.com

0
8/5/2004 10:47:41 AM
Sorry forgot to add that at the top of the sheet where all the button
are for entering data I have used the freeze panes attribute. Cells 
through 13. After this is where all the data is stored. 

Thank

--
Message posted from http://www.ExcelForum.com

0
8/5/2004 11:01:32 AM
One way:

Assuming you want the date in column A:

    Public Sub Button1_Click()
        Dim rTarget As Range
        On Error Resume Next
        Set rTarget = Intersect(Range("A14:A" & Rows.Count), _
                ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))(1)
        On Error GoTo 0
        If Not rTarget Is Nothing Then
            With rTarget
                .NumberFormat = "mm/dd/yyyy"
                .Value = Date
            End With
        End If
    End Sub

Change column references if desired.


In article <Paulwilson412.1aihpf@excelforum-nospam.com>,
 Paulwilson412 <<Paulwilson412.1aihpf@excelforum-nospam.com>> wrote:

> Thank you all so much!! I didnt realise you could do it so many ways.
> 
> Its works a treat!
> 
> On the down side its given me another problem....sorry to trouble you
> all again but....
> 
> now that I have entered the "TOADYS DATE" correctly I have a slight
> problem with the same date, and that is when I apply filter to the Data
> Card number (Spread sheet attached) so that it can be found easily,
> which brings it to the top, now when I press the Todays date button it
> obviously enters it into the wrong Cell....!!!???!!! How do I tell the
> Excel to enter the Todays Date into the Top row once filtered??
> Unfiltered the top row is 14.
> 
> Thank you once again for you kind help!
> 
> oops sorry file too big .
0
jemcgimpsey (6723)
8/5/2004 1:42:24 PM
But the date still inputs to row 14. Now when I use a filter and scro
down the page to a different row then press the Todays date button 
need the date to be input into the cell just below the Date ou
column.

I have added a Gif to explain myself better.

Thank you once again.

ps as I think I said before rows 1-13 have the freeze pane attribut
so dont change when scrolling down the page

               Attachment filename: untitled.gif               
Download attachment: http://www.excelforum.com/attachment.php?postid=63953
--
Message posted from http://www.ExcelForum.com

0
8/5/2004 5:16:33 PM
Your specification was:

> How do I tell the Excel to enter the Todays Date into the Top row 
> once filtered??

If row 14 is the "top row when filtered",  the macro will enter the date 
in Row 14. If Row 23 is the "top row when filtered", it will enter the 
date in Row 23.

When you mention scrolling, it sounds like you're not filtering at all, 
or at least not using "top row when filtered" the way I'm thinking of...

> I have added a Gif to explain myself better.

While I'm sure it was clear to you, to me the Gif added nothing (no 
filters are even applied, so Row 14 *should* be the right row to enter 
the date in, right?). This is not unusual - it's one reason I never open 
attached workbooks - something blindingly obvious to you is not 
necessarily clear to the uninitiated.  

Try using words.



In article <Paulwilson412.1aizpj@excelforum-nospam.com>,
 Paulwilson412 <<Paulwilson412.1aizpj@excelforum-nospam.com>> wrote:

> But the date still inputs to row 14. Now when I use a filter and scrol
> down the page to a different row then press the Todays date button I
> need the date to be input into the cell just below the Date out
> column.
> 
> I have added a Gif to explain myself better.
> 
> Thank you once again.
> 
> ps as I think I said before rows 1-13 have the freeze pane attribute
> so dont change when scrolling down the page.
> 
>                Attachment filename: untitled.gif                
> Download attachment: http://www.excelforum.com/attachment.php?postid=639537
> ---
> Message posted from http://www.ExcelForum.com/
0
jemcgimpsey (6723)
8/5/2004 5:48:39 PM
ok, say I need to view just one data card, say on row 43. Now i've use
the filter above the data card entry to just select this row. So no
the rows go from 1-13 then the next row will be 43.
Now this is when I need the date button to work by inputting today
date just on this row and in cell (which for this instance is I43).

does that make more sense 

--
Message posted from http://www.ExcelForum.com

0
8/5/2004 6:44:09 PM
Yes, and that's what the macro does...

Rows 14:42 are hidden and the entry will be made on row 43. If you 
changed "A" to "I", the date will be input in I43.

Here's a link to my test workbook:

    ftp://ftp.mcgimpsey.com/excel/paulwilsondemo.xls




In article <Paulwilson412.1aj3rj@excelforum-nospam.com>,
 Paulwilson412 <<Paulwilson412.1aj3rj@excelforum-nospam.com>> wrote:

> ok, say I need to view just one data card, say on row 43. Now i've used
> the filter above the data card entry to just select this row. So now
> the rows go from 1-13 then the next row will be 43.
> Now this is when I need the date button to work by inputting todays
> date just on this row and in cell (which for this instance is I43).
> 
> does that make more sense ?
0
jemcgimpsey (6723)
8/5/2004 7:10:52 PM
Thank you for you help

--
Message posted from http://www.ExcelForum.com

0
8/6/2004 6:45:03 AM
Reply:

Similar Artilces:

Bank Loan. Due Date Follow Up, Email Reminders
Hi guys. As our business is growing, we were handled a significant amount of money from the bank. The money is collected each time we give the bank an oficial document stating the value of the product we are commercializing. In GP 8, what is the best way to handle these money loans?, they have a termination date and we are obligated to pay the loaned money plus interest. Is GP 8capable of sending reminders? Thanks in advance Daniel, Sounds like this may be handled by the Payables module together with either Revenue/Expense Deferrals or the Scheduled Payments. You should be able to ...

Change date from mm-dd-yyyy to MMM-YY in Access
How do I change a field which contains a date in a mm-dd- yyyy format into mmm-yy format. For Example: Ship Month = 03/01/2004 Convert "Ship Month" value to display "Mar-04" Hi Vince, Set the Format property of the field, and/or the control(s) that are displaying it, to "mmm-yy" On Tue, 20 Apr 2004 12:01:52 -0700, "Vince" <anonymous@discussions.microsoft.com> wrote: >How do I change a field which contains a date in a mm-dd- >yyyy format into mmm-yy format. > For Example: Ship Month = 03/01/2004 > Convert "Ship Month&qu...

i cannot use the mouse right click in excel. how to rectify?
i cannot use the mouse right click in excel. how to rectify? Is this just when over the cells ? Does it work on the title bar - windows options Or on the Excel menus ? Steve On Tue, 19 Sep 2006 14:21:01 +0100, j <j@discussions.microsoft.com> wrot= e: > i cannot use the mouse right click in excel. how to rectify? Unless the right-click button is frozen, I'm sure you can use the mouse right-click in Excel Just hit the right-click button. Now...........................the real issue What are you right-clicking on and what doesn't happen for you? Does the right-click no...

Using exchange server 2003 as an smtp server outside of the domain
For the life of me I cannot figure out how to setup exchange server as an smtp server so that I can use it to send mail from outside of its domain environment. basically i want to be able to send email from any computer not on the domain by using mail.mydomain.com . I'm not sure if the issue is actually related to something on exchange server 2003, or if it's DNS related issue. we do use OWA so wthat when we type in a web browser mail.mydomain.com it takes us to OWA. Right now if I try setting up a POP3 Account in Outlook and use mail.mydomain.com for incoming and SMTP servers it err...

Grid view buttons
I have added a button to account grid view. this button would open a new page(Custom page). There's submit button in the custom page. when i click that button the same custom page gets opened. this doens't occur if i change the winmode of this button to 0. for 1 and 2 values this scenario occurs. the issu is that i cannot use the 0 option as the window.dialogargumants return as undefined. How can i stop this new page being opened?. Cheers, Charith ...

Calc date one year from today
Im trying to calculate a date range that will monitor warranty (364 days) from date of purchase order ?? Thanks for the help Ken Hi Ken! Use: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1) It will even cover the difference between Leap Years and non-Leap years which your 364 has a problem with. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Ken" <anonymous@discussions.microsoft.com> wrote in message news:11d0601c3f626$44145840$a301280a@phx.gbl......

Refresh Form
I have an interesting problem (maybe its not so interesting to the gurus who frequent this board :) ). Here is the back ground information: I have a form that contains tab controls and several sub forms. The main form contains a control that dictates what data is displayed on one of my sub forms via parameters in the query (data source). So, I have added some code to the "after update" event that looks like this: --------------------- me.form.refresh me.form.requery --------------------- I have also tried: --------------------- Me.[sub_form_control_name].Requery -----------...

Datagrid width on a Web Form
Hi all I have a datagrid in a web form and I want to be able to determine the width of the datagrid at runtime. I have tried both of the following // Get the width using the datagrid width attribute. double datagridWidth = 0; datagridWidth = dgProjects.Width.Value; and // Add the widths of all columns double dgWidth = 0; for (int i = 0; i <= dgProjects.Columns.Count - 1; i++) { dgWidth = dgWidth + dgProjects.Columns [i].ItemStyle.Width.Value; } Neither of these returns the wi...

Using CIPAddressCtrl in stead of an edit control
If I use a CIPAddressCtrl on my property page it does a great job of handling the 4 bytes with periods but how do I get the address in and out of the property page in the DataExchange function? Do I have to handle the DWORD as the exchange data? Rob DDX_Text should work... "Rob K" <rbk@no.spamm.net> д���ʼ� news:3zgsf.10739$SM6.8164@fe15.lga... > If I use a CIPAddressCtrl on my property page it does a great job of > handling the 4 bytes with periods but how do I get the address in and out of > the property page in the DataExchange function? Do I have to handle th...

using one combo box to determine content of a second combo box
Hello, I have been trying for several days to figure out how to use one combo box to determine the content of a second combo box. I am creating a database listing equipment by manufacturer and model # I would like to create one combo box that gives a drop down list of manufacturers Then, based on the manufacturer chosen, I would like to have a second combo box which gives a drop down list of model #'s offered by the chosen manufacturer It seems like a fairly simple concept, but I have poured through the "using access 2003" guide with no luck Access version I'm using: 20...

Image in Form
Some forms templates in acc 2007 has image to create forms, on the top, it looks a web page. Unfortunately I can not manipulate these images to customize. The images are embedded. Somebody else knows where can I find images, similar pictures? Thanks ...

Date reference in an IIF statement
The following statement works well. It is the default value for a payment. However, every year I have to go in and change the year. =(IIf(Date()>#3/31/2010#,"$45.00","$40.00")) I would like to have it look at 3/31/(of whatever year it is) so I dont have to reprogram this every year. How do I change the date reference? Thanks in advance. On Wed, 7 Apr 2010 09:16:01 -0700, Presto wrote: > The following statement works well. It is the default value for a payment. > However, every year I have to go in and change the year. > > =(IIf(Date...

Highlighting just one record in an unbound form
Long time user of Access, First time programming it. I have an unbound form that takes employee ID's and work order job information. When they arrive on the job they call in and I run an app to enter the timestamp of when they arrived. I may have a list of employees with jobs. My question is how do I or can I highlight (Bold) the records that I have already timestamped? I have be trying to use FontWidth but I keep getting all or nothing Bolded. I tried to look at the Arrived field and if it has no value yet to regular text. If it has a value then Bold. Any suggestions?? Th...

error with the form when using asp.
I get this error, when i submit the form. The error comes up and some technical advisor from my host company told me to change it to ASP Email but i don't understand what he was talking about. Can someone provide assistance. Server object error 'ASP 0178 : 80070005' Server.CreateObject Access Error /WebAppSub.asp, line 9 The call to Server.CreateObject failed while checking permissions. Access is denied to this object. ...

I don't know which formula to use?
I need help understanding which formula I should use. . . I have built a table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to ...

dynamic forms (i.e. form with hidden text boxes, calenders etc)
I have one form with 25 different text box, calenders and selection boxes on the are all hidden except one showing at a time (its being used for users to entered data). I need to put a progress bar, but as the number of input choices changes dependant on what the user enters, its not as simple as '1 of 25' the other idea is to put all the 25 catorgories on one side of the form and then display what the user has entered as they go along. (problem here is that it means 25 new text boxes !!) any one got any other ideas? This would be very hard as you stated you never know how man...

Please help me: Refresh pivot table using another source file.
My supervisor put an interactive spreadsheet (or pivot table) on the web. Now he wants me to make changes to the spreadsheet so that when you click "Refresh" on the website, this spreadsheet gets new data from me rather than from my supervisor. How do I do this? Thanks for your help. ...

Add Collapsible data to report or form
I want to show a report with the following levels: Asset Class Investment Option Dealer Group So within each asset class there will be options then within each option will be dealer groups. Easy enough. Now what I want to do is somehow show something like this.. [-] Asset Class [-} Investment Option 1 Dealer Group 1 Dealer Group 2 the [-] are collapsible levels. So I want to initially display all asset classes and how much is in them, then allow the user to click on the report, or form and then display underneath the asset classes the investment ...

Payment Terms for specific date
Hi! Our client needs to have Payment Terms for specific date. For example, they want it to be Jun 1, 2009 They sell product but customer doesn't have to pay until June of 2009 Can this be accomplished using GP functionality? If not, what is the best way to customize/make it work? Thank you, Ivan Not sure how you would do this without a customization, but here is what you can try. I assume your customer is running 6-months no interests, no payments type thing for the month of December (again, just an assumption). 1) You could conceivably setup a payment term called JUNE012009,...

Can't remember which Product Keys I have used
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Hi-I have Microsoft Office 2008 for Mac (Home &amp; Student Edition). It came with 3 product codes. I used one of the product codes and I think that some of my family used one or both of the remaining 2 codes. The problem is that I am not able to verify if this is true or not. <br><br>So my questions is - HOW DO I DETERMINE WHICH PRODUCT CODES HAVE BEEN USED AND WHICH ONES ARE STILL AVAILABLE TO USE FOR MY SOFTWARE? <br><br>Thank you to anyone who can help! ...

Duplicating data according to Dates
Well, that's the best description I can think of anyway. I need help with a solution. I have a set of data (vehicles) that I have managed to extract a complete history for. This dataset shows the month and year of every inspection that was completed on the vehicle according to the location that it was at when the inspection took place. Here is an example: Vehicle -- Location -- Year -- Month Car1 -- Location1 -- 2006 -- 9 Car1 -- Location2 -- 2006 -- 12 Car2 -- Location1 -- 2007 -- 2 Car2 -- Location2 -- 2006 -- 11 Car2 -- Location3 -- 2007 -- 5 Car3 -- Location1 -- 2006 -- 9 My goal is...

Need To Add VBA Tools Like A Date Picker
I have written a couple of VBA projects for my company. Most of the computers run them all fine but some do not. About half of the computers are running Windows XP Pro w/ Office XP Small Buisness and the other Half are running on Windows 2000 Pro w/ Office 2000 Small Business. My problem is that some of the office 2000 computers do not have Date Pickers in there VBA so they get errors when they try and run the programs. It says something like error: project cannot be found. I want to know where I can update my VBA tools from. Any help would be greatly apprecitated. Thanks, Mark ...

Unable to use Microsoft Outlook
I'm using a Dell 4700C, Windows XP Professional, Internet Explorer 6.0. I am unable to send or receive mail in Microsoft Outlook. Thank you for your help, meadow "meadow" <meadow@nospam.com> schrieb im Newsbeitrag news:uGk9sj3$FHA.980@tk2msftngp13.phx.gbl... > > I'm using a Dell 4700C, Windows XP Professional, Internet Explorer 6.0. > > I am unable to send or receive mail in Microsoft Outlook. > > Thank you for your help, > > meadow > Hi Meadow, tis is a little bit less information to help you. What Outlook version? Do you use Excha...

go through the records in a form from code
i have a table tblPay for Payments and a tblEmp for employees (i have a lot more but this are the ones involved in my problem) when i go through the records in the form for employees it auto fills a yes/no field to know wich ones are overdue in their payments, based on tha tblPay, this works fine except that it doesn't get filled UNTILL i go through the records 1 by 1 with the form, if i add a new payment and open the table Employees it doesn't shows it, i have another form where i need to use that yes/no field so i need a way to open the frmEmp and make it go through all the records a...

Emailing from a form using recursive query results
I am trying to create a button on a form (form name is [BG Paper Info]), when cliking the button it should do the following things: 1. Activate a query [BG Email from AE Code] that uses a Control [AE Code] that appears on the form (which can change from record to record), in order to generate an Email address ([EmailAddress] in the query)to which to email a report. The report is generated using another query [BG Reminder letter] that contains a memo field with the text of the letter, the text depends on a code [Reminder Type] that appears in the form. The two queries and the report operate ...