The date & time of a cell is input 2

[Robert1688]

I am creating a database which for inventory items stock. Is that possible to show the date and time in a cell of the same row where and when I input stock information?

 

[yogia]

Hi Robert:

Please provide a better description and post some sample data so we can clearly see what you want to to do.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Robert1688]

Like this:
PartNo PartStatus Qty Date & Time
123456 Received 200 2007-4-13 16:33
123457 Sent 100 2007-4-14 9:23

I want the "Date & Time" input automaticly. For example, as the time input the Qty.

 

[IRstuff]

A simple approach is to put the following in a macro:
Range("DateTime").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

You use the function NOW() placed in a cell with a defined name of DateTime

You assign it to a floating button. once you enter the data, you select the empty cell and push the button. The macro copies the timestamp and pastes it as hard data into the empty cell selected.

TTFN

FAQ731-376

 

[yogia]

Hi Robert:

One way would be to use Worksheet_Change event ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And IsNumeric(Target) Then Target(1, 2) = Date & Time
End Sub

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Robert1688]

Thank you IRStuff, that helps, but it seems not so convinent. If a cell is entered, is there a code we can use in VB?

I improved you marco,
Selection.NumberFormat = "m/d/yyyy h:mm"
Range("DateTime").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

yogia, I don't know how to use it, sorry

 

[joerd]

If you want to be really quick, press Ctrl-; for the date, and Ctrl-Shift-; for the time.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yogia]

yogia, I don't know how to use it, sorry

Hi Robert:

The Worksheet_Change event code I provided is triggered automatically whenever a numeric is entered in column C ... then in the corresponding cell in column D current Date and Time are automatically entered.

To install the Worksheet_Change code, copy the code as I provided, then right click on the worksheet tab and click on View_Code -- that will take you to the VBA code window -- paste the code in there. Click ALT-F11 to return to EXCEL worksheet environment and the code will be in-waiting ready to be triggered whenever a numeric is entered in column C.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Robert1688]

Thanks so much, yogia, it works.
Normally, I just use VBA to build some functions for myself. You opened a new door for me, thanks again.

 

[yogia]

Hi Robert:

You Are Very Welcome ... now let us keep EXCELing!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[franzdom]

It could be handled also with a very simple if function in the cells,
=if(b2="","",today())

 

[IRstuff]

Wouldn't the date would change every time the sheet gets recalculated in that instance?

TTFN

FAQ731-376