Solved

AutoLock will not allow cell to populate with time.

Posted on 2012-04-08
9
212 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 33

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 33

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 33

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 33

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

730 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