Database Issues

praneethj13

New Member
Joined
May 22, 2011
Messages
1
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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...
 
Upvote 0
Hi PJ,

Welcome to MrExcel.

Is this what you require?...


Excel Workbook
AB
1Date/TimeData Entered
222/05/2011 * 12:24:43 PMMrExcel
3**
422/05/2011 * 12:25:35 PMTest
522/05/2011 * 12:25:44 PMPJ
6**
722/05/2011 * 12:25:49 PMAK
8**
9**
1022/05/2011 * 12:25:53 PMTest
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...
Code:
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
AB
1Date/TimeData Entered
222/05/2011 * 12:36:02 PMPJ
3**
422/05/2011 * 12:36:10 PMAk
522/05/2011 * 12:36:20 PMTest
6**
722/05/2011 * 12:36:34 PMTest
8**
9**
1022/05/2011 * 12:36:24 PMMrExcel
Sheet1



Good luck.

Ak
 
Upvote 0
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.
 
Upvote 0
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....

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top