AutoLock will not allow cell to populate with time.

I have enclosed the spreadsheet with code.  The password is "password". I'm realy at a loss because all I did was make some changes on spreadsheet nothing in code section.  I have completely remade the sheet, tried to ad changes to existing workbook and nothing works.  I have checked to code and it all seems to be ok.  What am I missing?  Thank you
SC-Surveilance-Form.xlsm
dwestbergAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
When should the time be updated/changed?

Is it meant to happen via the code in the sheet module?

The code there will be triggered by any change on the worksheet - the locked cells can't be changed or selected so they can't trigger the code.
0
dwestbergAuthor Commented:
Imnorie

The code is the trigger.  when time is entered (Mouse Click or cursor) the time is filled in and the cell is locked.  The problem is the cell is not unlocking to let time populate.  I've placed password on sheet first then in editor and vise versa. Something is not unlocking the cells for input.

Thanks
Dave
0
NorieVBA ExpertCommented:
Dave

What code is meant to be triggered?

It can't be the Selection_Change or Change as you can't select or change the cells where you want to put the time as they are protected.

When do you want the time to be put on the worksheet?

Could that be decided by data being entered in another column?

eg when the user selects the Camera Error Report, or enters Observartions.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

dwestbergAuthor Commented:
Imnorie

It is the Worksheet_SelectionChange.  Cursor is positioned in Camera Error Field and when Operator complets session they use the mouse or arrows to enter time.  Time cell is suposed to let you enter one time which jpopulates the time, once time is entered cell places you back in Camera Error field.  You cannoit change any time once entered.  This code has been in use for at least 2 years and no problems have occured until now.  Macros are enabled, trust center, etc. have been enabled.  If you erase Password and leave "" you will see that the when you enter the Time field it populates with current time.  I don't think an If statement would work but have never realy thought of it.  How would cell lock after data (time) was inserted?  (=If(E5<>"",If(C5="",Now(),C5),"").  Could you not just go Back to Camera Error and change the time.  What would lock Camer Error?

I will try anything to get this to work.  Bottom line is time can't be altered and has to be user freindly.

Thank you for your time
Dave
0
NorieVBA ExpertCommented:
Dave

You can't select the any cells you want to put the time in if the worksheet is protected.

When the sheet isn't protected you can select a cell and the code puts the current time in the cell but the change event isn't triggered because you've disabled events.

Try testing it, with and without a password, by putting breakpoints (F9) on the first line of both subs.

To me anyway it's not clear what you actually want to protect.

Do you just want to protect the cell(s) that the time has been added to?

Try this.

Select all the cells on the worksheet, goto Format>Cells... and on the Protected tab uncheck Lock.

Then remove the change code and replace the selection change code with this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("C5:C390,G5:G390,K5:K390")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect "Password"
    Target(1).Value = Now()
    Target.Locked = True
    ActiveSheet.Protect "Password"
End If
    Application.EnableEvents = True
End Sub

Open in new window

This will put the current time in the selected cell and lock that cell afterwards.
0
dwestbergAuthor Commented:
Imnorie

I tried your Selection Change and the time does not populate. I realy don't know what the problem is.

Dave
0
NorieVBA ExpertCommented:
Dave

Did you remove the Change code?

Another thing to do is to open the Immediate Window (CTRL+G), type and enter this.
Application.EnableEvents = True

Open in new window


This is because there could be a chance that at some point events were disabled and never enabled again.

I'll also attach the file with the changes I made.
SC-Surveilance-FormV2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwestbergAuthor Commented:
Imnorie

Thank you so much for your help and your time your solution works great.
0
dwestbergAuthor Commented:
Thanks again for everything
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.