Solved

AutoLock will not allow cell to populate with time.

Posted on 2012-04-08
9
213 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:dwestberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37821672
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
 

Author Comment

by:dwestberg
ID: 37821813
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
 
LVL 34

Expert Comment

by:Norie
ID: 37821836
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:dwestberg
ID: 37822103
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
 
LVL 34

Expert Comment

by:Norie
ID: 37822293
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
 

Author Comment

by:dwestberg
ID: 37823849
Imnorie

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

Dave
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 37824152
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
 

Author Comment

by:dwestberg
ID: 37824262
Imnorie

Thank you so much for your help and your time your solution works great.
0
 

Author Closing Comment

by:dwestberg
ID: 37824271
Thanks again for everything
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question