Solved Auto create time and date in Excel 2010

November 2, 2015 at 07:41:12
Specs: Windows 7
In excel 2010 new sheet, I would like to enter order number on column A1 then column B2 will show time and date automatically then after entered data then A1 and B1 should locked the cell and won't change anymore. Is this possible to do it?

Thanks



See More: Auto create time and date in Excel 2010

Report •

✔ Best Answer
November 2, 2015 at 13:19:44
If B2 will be empty the first time an order number is entered, then this code will enter the date and time once and will not change for subsequent changes to A2.

If there will already be something in B2, then things get more difficult and I will need more specifics.

(BTW...I am assuming the A2 and B2 are just examples. The code actually applies to the entire Columns A & B)

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A
'and respective row in Column B is empty
  If Target.Column = 1 And Cells(Target.Row, 2) = "" Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet
    ActiveSheet.Unprotect Password:="zzzzz"
     Cells(Target.Row, 2) = Now
    ActiveSheet.Protect Password:="zzzzz"
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
November 2, 2015 at 07:50:33
This statement : "then A1 and B1 should locked the cell and won't change anymore" can mean 2 different things:

1 - The date and time should be placed in the cell as a "hard coded" value, not a formula, but the cells can still be manually edited.

2 - The date and time should be placed in the cell as a "hard coded" value, not a formula, and the cells should be locked and protected so that users can't change them.

Both of those results can be accomplished automatically with a macro, but Option 2 takes a bit more work, so I want to make sure I understand your full requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 2, 2015 at 08:12:23
#2 is the option that I could like to use, because the user might accidently edit the cell that original data already been there, that's why I could like to have protection on the cell after they enter it. Appreciate your help on this.


Report •

#3
November 2, 2015 at 09:50:05
This should get you started...

The first thing you have to do is Unlock all cells that you want users to enter data into.

Format...Cells...Protection tab

In order to lock a specific range, you must protect the entire sheet. By Unlocking certain ranges, the sheet can be protected but users can still enter data where you want them to. For example, if the only range you are trying to protect is Column B, then Unlock every Column except for B.

Right click the sheet tab for the sheet you want this to work in and paste this code into the pane that opens. Note that the password is set to zzzzz. You can change that to whatever you want the password to be.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A
  If Target.Column = 1 Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet
    ActiveSheet.Unprotect Password:="zzzzz"
     Cells(Target.Row, 2) = Now
    ActiveSheet.Protect Password:="zzzzz"
  End If
End Sub

Please note that unless you hide and password protect the VBA code itself, users will be able to see it, change it, find the password, etc. See here for the instructions on how to protect the code:

http://www.exceldigest.com/myblog/2...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 2, 2015 at 12:28:46
Thanks for your help

At column A2 enter order number then Column B2 auto generated date and time that I want to be. and then Column B is locked. Thanks :)

However, if I re-enter on A2 order number then B2 date and time will be updated.

Is this possible B2 date and time permanent lock without change even I change work order number on A2? because we try to save the original date and time when first time enter order number.

Thanks a lot.


Report •

#5
November 2, 2015 at 13:19:44
✔ Best Answer
If B2 will be empty the first time an order number is entered, then this code will enter the date and time once and will not change for subsequent changes to A2.

If there will already be something in B2, then things get more difficult and I will need more specifics.

(BTW...I am assuming the A2 and B2 are just examples. The code actually applies to the entire Columns A & B)

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A
'and respective row in Column B is empty
  If Target.Column = 1 And Cells(Target.Row, 2) = "" Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet
    ActiveSheet.Unprotect Password:="zzzzz"
     Cells(Target.Row, 2) = Now
    ActiveSheet.Protect Password:="zzzzz"
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
November 2, 2015 at 14:58:19
Thank You so much your quick solution on this.

Report •

Ask Question