Input a date using a Form Button.

P

Paulwilson412

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!
 
H

Harald Staff

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
 
J

JE McGimpsey

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.
 
R

Rollin_Again

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
 
P

Paulwilson412

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
 
P

Paulwilson412

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
 
J

JE McGimpsey

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.
 
P

Paulwilson412

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
 
J

JE McGimpsey

Your specification was:
How do I tell the Excel to enter the Todays Date into the Top row
once filtered??
Click to expand...

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.
Click to expand...

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.
 
P

Paulwilson412

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
 
J

JE McGimpsey

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
 
Top