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
251 Views

Similar Articles

[PageSpeed] 43

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:

missing buttons #2
I just installed the new Office 2003 edtion. I went into e-mails and my send button and now my paper clip button are missing. I've checked in where you can add buttons but they're not in their either. What can I do to find them. Are you using Word as your e-mail editor? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:4E889BAD-E127-4AF8-BC98-7AB117D0B90B@microsoft.com, Sharon wrote: > I just installed the new Office 2003 edtion. I went into e-mail...

Cannot log in using WindowsID (was able to before)
I just bought MS Money 2007 Deluxe yesterday. The install went smooth, without any issues. I registered a new WindowsID and was able to successfully log into MS Money. However, today when I tried to log into MS Money, the messge apeared that the "Money was not able to recognize your WindowsID" or something like it and that "You will still have access to some functions but will not be able to make online updates". Now I'm not able to make updates. Any idea what this could be? Is the authentication server for WindowsID down or something? See the post under Mon...

Junk button in windows live accidently pressed
Whenever I open up WLM i press sync automatically as a bad habbit. Because of the loading time, the junk button usually gets inserted before the sync button, pushing it out to the right after its loaded. I therefore accidently press the junk button by accident sometimes. This is a classic Microsoft HCI problem. Surely the button should be there but greyed out when you cant click it. Having buttons moving around in menus is bad in the same way popups are bad. So break your bad habit WLM will sync automatically when 1st opened peter -- If you find a posting or message...

Can't use Merge feature
If I have a postcard file I've already made on the screen, when I try to pull up the "Merge" feature under Tools, it is shaded grey and I can't open it. It insists that I either form a new database list of addresses or edit the one that I want to merge. I don't want to edit the one I want to merge. I just want to MERGE it. If, however, I have just a postcard template on the screen (without it having been made into anything), I CAN pull up the "merge" feature under Tools. It is not shaded out. How do I un-shade the "merge" feature? To merg...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Restore Exchange 5.5 on 2000 using ArcServe
I've got - Windows NT 4 Server SP6 - Exchange 5.5 SP3 - ArcServeIT 6.61 with Exchange Agent I'm trying to restore the Exchange information (database, mailbox... everything) on another computer. That computer has - Windows 2000 Server SP4 - Exchange 2000 - Brighstore ArcServe 9 (build 2020) with Exchange Agent ------------------- Now the situation: I need to verify that the backup was made correctly (entirely) in order to confirm the total validity of the backup (to validate my disaster recovery plan) and to update to SP4. I tried to find documentation with both Microsoft (for Ex...

How to use structured reference (ListColumns) in VBA
I want to do something simple...loop through a range retrieving values one at time and taking action on that value. This range is an Excel 2007 table column. Easy enough...BUT I want to use structured references to a table column by NAME, not numbers. I don't want to define new names, just use the table column headings. I've found ways of doing it with quotes, but that's not structured....the names in quotes do not change if the table heading is changed by the user. The formulas change if the table column heading is changed, but I want it to change in VBA to...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

Hot Dates
Hi, I have a Year-at-a-Glance worksheet into which users enter important dates and associated actions to be taken. I now want to use Conditional Formatting to highlight specific entries in the worksheet when their date matches today's date. The problem I have is that I need to convert data from three independent cells into a date to compare it with 'TODAY()'. The first cell (formatted as a number) carries the day, e.g. 2, 17, 23, etc. The second cell (formatted as General) carries the month, e.g. January, March, September, etc. The third cell (formatted as a num...

Using Excel in Mac office 2004 with Microsoft/Mac 2008:Analysis toolpack
Although I am disappointed with Office 2008 for Mac because of the absent of Analysis toolpack, I figured things out quite recently that I can still install my Mac office 2004 and use the excel package that contains the Analysis tool pack. How? ...... let me show you: 1. Insert your CD for Mac office 2004 2. When it open on your desk top, two options will be given to you to download the software (a) Office set-up assistant, and (b) Microsoft office 2004. 3. CHOOSE MICROSOFT OFFICE 2004 (Drag the icon onto your desk top) and it will start to run/download. 4. When done, this will appear as ...

How to obtain enumerated attribute declarations from a DTD using MSXML & VB?
I have a VB (VB6, not .NET) application that reads an XML file (using MSXML v3.2 parser); the XML file contains a reference to an external DTD. The DTD has numerous enumerated attribute declarations like so: <!ATTLIST MyElement MyAttribute (One | Two | Three) "One" > The VB code needs to generate dynamic pick-list for all the attributes that appear in the DTD as part of a data-entry application; each dynamically created pick list should contain all the allowable values for the attribute as defined by the DTD (and presented in some kind of multiple choice control like a comb...

Using Excel spreadsheet as input to Access
Hello, I posted this in the New Users forum but only got one answer, so thought I'd try here as well. Like so many others, I am an Excel newbie. I was a mainframe COBO programmer in another life, but that was a few years back My manager would like me to write an app that will take tracking dat from an existing Excel spreadsheet (generated by our system) but onl use a select handful of columns as input to a new Access database tha I will create. I'm guessing that I can either a) create a new edited spreadsheet to b used as input to the Access database or b) use the Import wiza...

Insert Static Date as part of Macro
I've built a nice macro, but I want it to insert the date it is run in an empty cell within the macro's range. When working within a spreadsheet, Ctrl +; returns the system date, but I can't figure out how to put that command into a macro. The macro recorder editor shows that the system recognizes the entry as the current date, not a function that returns the current date. The now() or today() functions are both dynamic ... they change with the system date. That's not acceptable in this situation. I need to seen when the macro was run. Thanks for any help, Ralph di...

Using Risk+ with MSP 2007
We use Risk+ as a risk simulation tool. We have discovered that it works approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run into this problem. If so, is there a remedy? My hunch this not a generic issue, but surely best that you consult with the Risk+ people on this. --rms www.rmschneider.com On 02/03/10 18:08, Tom Mc wrote: > We use Risk+ as a risk simulation tool. We have discovered that it works > approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run > into this problem. If so, is there a remedy? W...

Autofilter reset button
I recently saw a spreadsheet (don't have the file) that used an Autofilter for the column titles on a freezed pane. Very standard set-up that most of you are well aware of. The big difference is that a Form-type button coupled with a macro was set-up to reset all Autofilters in use. All you did was press the button, named "Reset Filters" and voila - every user-defined Autofilter was reset back to the "All" setting. How can I replicate the same thing? Actually, I think you can record that macro. http://www.officearticles.com/excel/record_a_macro_in_microsoft_e...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

zz report date truncation
We run only z reports at end of day. We will be running cashier shifts soon and would like to run z and zz reports the way they were intended. Is there any way to truncate the zz report date for each register without it running since the beginning of time and using a truckload of paper? Thanks in advance! This may not be the most graceful solution, but you could simply turn off your printer before 'printing' the zz report. Then manually delete all print tasks for that printer before turning it back on. The report won't print, but RMS will think it did. "TV hardwa...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

Historical IV Trial Balance
When running this report in Summary, I am receiving an error when trying to run with a date range. Has anyone seen this before? are you getting a duplicate error? If you are, then what you are experiencing is a bug. To fix, you will have to apply the latest service pack. Hope this helps. Rheiner "DavidInterDyn" wrote: > When running this report in Summary, I am receiving an error when trying to > run with a date range. Has anyone seen this before? I think so. I'll try that by applying SP 4 for 10.0 "Rheiner" wrote: > are you gettin...

how to use roundup
how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 to 1251000 , 1250750 to 1250800 thank alot =ROUNDUP(A1,-2) -- Kind regards, Niek Otten "r4319hb" <r4319hb@discussions.microsoft.com> wrote in message news:A8D5EA24-858C-49D3-A5ED-481FEBEA36CB@microsoft.com... > how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 > to > 1251000 , 1250750 to 1250800 > thank alot =CEILING(A1,100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "r4319hb" <r4319hb@discussions.microsoft...

Excel
Hi, I would like to know if there is a way to set up when I enter the data each cells on first row, it will default the date and time stamp on first row. 2nd row should have the different date and time stamp than 1st row. I put "=now()" but it create the same date and time for 80 rows. I don't want that. I want to enter each rows and each rows should be different by one minute apart but same date - June 22. Your help would be much appreciated. Thanks Format A1:A79 as Custom dd-mmm-yy h:mm Select A1 Hold down CTRL Key and hit semi-colon key then <space> bar. Sti...

Using 'like' on a user prompt
I want to prompt the user for a value to use to filter a form. Here is my command with the WhereCondition: docmd.OpenForm "test", acNormal, , "LastName=[Enter a Last Name]" I want to use the WhereCondition as a "LIKE" criteria. Variations on the WhereCondition itself that I've tried include various combinations of LastName LIKE %...% LastName LIKE *...* LastName LIKE [%...%] LastName LIKE [*...*] You get the idea... No luck so far. Any thoughts? On Thu, 27 May 2010 21:52:50 GMT, "RicDon" <u60397@uwe> wrote: >I wan...