?
Solved

AutoLock will not allow cell to populate with time.

Posted on 2012-04-08
9
Medium Priority
?
219 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 35

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 35

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 35

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 35

Accepted Solution

by:
Norie earned 2000 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

864 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