Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DATES

Status
Not open for further replies.

asal

Technical User
Joined
Mar 14, 2001
Messages
8
Location
US
ON INPUTTING DATA IN ONE CELL HOW CAN I REFLECT THE CURRENT DAYS DATE IN ANOTHER CELL WHICH WILL NOT CHANGE AFTER MIDNIGHT.
 
Hi,
As you discovered, the formula =Today() changes with the day.

There are at least two ways to accomplish this.

First is using the formula/Copy/PasteSpecial:
1. Enter =Today() in the target cell
2. Copy the target cell.
3. Paste Special/Values in the target cell.

The second is to use VBA...
In the Sheet Object is Code for various events. One of the Events is WorkSheet_Change.
In the WorkSheet_Change code you could use the following...
Code:
    Dim  iColDateEntered As Integer
'--if you have contiguous column headings in Row 1
'  you could do this...
    For Each Heading In Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
        If Heading.Value = "DateEntered" Then _
            iColDateEntered = Heading.Column
    Next
'--this is where you set today's date ONLY if it is empty
    With Target
        If .Row > 1 Then
            If IsEmpty(Cells(.Row, iColDateEntered).Value) Then _
                Cells(.Row, iColDateEntered).Value = Int(Now)
        End If
    End With
Hope that one of these options help. :-) Skip,
metzgsk@voughtaircraft.com
 
right click on sheet name and select view code
then in the first drop down choose "worksheet" then in second choose change....

this sub will appear......
------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub
------------------------------------

then copy and paste the code as follows....


Private Sub Worksheet_Change(ByVal Target As Range)

'*******copy this code
If ActiveCell.Column = 1 Then Cells(ActiveCell.Row - 1, ActiveCell.Column + 1) = Date$
'*******end of copy code

End Sub

then.....
Change the "1" in "If ActiveCell.Column = 1 ", to corespond to the column number(I know it's letters, but you'll have to count) that user input will trigger the event.

example:
user inputs in col D then change to If ActiveCell.Column = 4



then make sure you macro security is set to low...

Tools>Macro>Security....select low
 
Thanks,Skip and ETID for your answers to my DATES
question.ETID'S solution was more like what I wanted.
May the Force be with you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top