Solved

AutoLock will not allow cell to populate with time.

Posted on 2012-04-08
9
209 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
  • 5
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:dwestberg
Comment Utility
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
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.

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Imnorie

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

Author Closing Comment

by:dwestberg
Comment Utility
Thanks again for everything
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now