Database Issues

[praneethj13]

Hi guys,...

I am trying build a database kind of thing, and i have 2 doubts regarding this, hope you guys will help on this..

1. Is there possible to create time stamp,.. when the record is entered. Tried something with now() function A1= if(B1>0, now(),"").. A2, A3, so on ..but if something is entered in B2, both A1 & A2 with change to present time...

2. How to lock certain cells.. I mean the formulae in the cell should be active and any change in input values must change the o/p value of the cell according to formulae.. But the user must not be able to change the cell once locked or something like that.. or should have password or something like that...

Advance thanks
PJ

 

[pushkardey]

as long as B1 will be greater than 0, Now() will always change to the current system time....

I dont know if this can be done by formulae.... I think you will require VBA...

 

[Akashwani]

Hi PJ,

Welcome to MrExcel.

Is this what you require?...

Excel Workbook
	A	B
1	Date/Time	Data Entered
2	22/05/2011 * 12:24:43 PM	MrExcel
3	*	*
4	22/05/2011 * 12:25:35 PM	Test
5	22/05/2011 * 12:25:44 PM	PJ
6	*	*
7	22/05/2011 * 12:25:49 PM	AK
8	*	*
9	*	*
10	22/05/2011 * 12:25:53 PM	Test
Sheet1

When data is added to column B the cell next to it in A will have the date & time entered.

Right click the sheet tab that you want this to apply to and click view code.
Paste this code into the white area that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        ActiveSheet.Unprotect "Password1"
        Application.EnableEvents = False
        Cells(Target.Row, 1).Value = Date + Time
        Application.EnableEvents = True
        ActiveSheet.Protect "Password1"
    End If
End Sub

If you UNprotect the cells in B and Protect the rest of your worksheet with Password1, then when the date & time has been added the user will not be able to change the date & time stamp, but it will change if they re enter data in column B....

Excel Workbook
	A	B
1	Date/Time	Data Entered
2	22/05/2011 * 12:36:02 PM	PJ
3	*	*
4	22/05/2011 * 12:36:10 PM	Ak
5	22/05/2011 * 12:36:20 PM	Test
6	*	*
7	22/05/2011 * 12:36:34 PM	Test
8	*	*
9	*	*
10	22/05/2011 * 12:36:24 PM	MrExcel
Sheet1

Good luck.

Ak

 

[earthworm]

i want the now function to stop working on single cell
and i want the now function to work on next cell when the data is entered

i other works i want to stop the time stamp for each cell different so that i can know at what time on each cell value has been entered .how to achieve it please advice.

 

[Akashwani]

Hi,

You can use the code I have provided and change the following to suit your columns....

If Target.Column = 2 Then
*The 2 refers to column B

Cells(Target.Row, 1).Value = Date + Time
*The 1 refers to column A

So if you were entering data into column H and wanted the date/time in G you would put....

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
        ActiveSheet.Unprotect "Password1"
        Application.EnableEvents = False
        Cells(Target.Row, 7).Value = Date + Time
        Application.EnableEvents = True
        ActiveSheet.Protect "Password1"
    End If
End Sub

I hope that's the information you require.

Ak