Auto Lock Cells once data is entered not working.

[garyreid81]

Hi all, first time posting.

I have searched and found many examples of code that will lock cells once data has been entered. But for some reason, I cannot get it to work in my workbook. The weird thing is, I have successfully got them to work on blank workbooks. I unlock a range I want people to be able to edit, I protect the workbook, I enter the code, and it works. But I do the same thing on my workbook, nothing locks. Can anyone think of any possible reasons for this? I've tried locking all the cells on a sheet, and only unlocking a small range, as in the examples (A1:A10), and I cant get it to work. I've tried not pre-protecting the sheet, I've tried 4 or 5 different examples of code. If it wasn't for the fact I've gotten it to work on blank workbooks, I'd think VBA was disabled or something. I can't post a copy of my workbook for you all to look at because its on a stand-alone computer at work (and the USB is disabled for info-sec).

When I hit 'View Code' on the sheet, and paste in the code, is there something else I need to do? It's almost as if the code is going in, but not running? I'm not a complete programming noob (did C++ back in the day), but VBA is new to me.

Excel Version 2007

Thanks

[Tsjallie]

As I read from your post your company maintains a pretty tough security policy.
So did you check the default security setting of Excel?
Specially the setting for macros. Maybe they are disabled.

[garyreid81]

I thought that too, but why then can I get it to work on a blank workbook? Its got to be something in my workbook.

[cvishu]

check the sheet name if its same as in the code

[garyreid81]

Hey, I've tried that as well with certain examples of code that require the sheet name. I've also tried examples of code where no sheet name is entered.

[sktneer]

To check if the code on worksheet module is running or not, open the code and remove Sub from End Sub (the last line of code) and comeback to your sheet and enter a value in the range specified in the code. If you get the Compile Error: Expected End Sub, that means your code is running, add the Sub after End in the last line and debug the code by pressing F8 to see what that code is exactly doing.

[cvishu]

try this module (the only problem even if you press delete by mistake that cell will get locked)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

Set MyRange = Intersect(Range("A1:O1000"), Target)
If Not MyRange Is Nothing Then
Sheets("Sheet1").Unprotect Password:="1234"
MyRange.Locked = True
AllowFiltering = True
Sheets("Sheet1").Protect Password:="1234"
End If
End Sub

[garyreid81]

Hey, Thanks for the reply. I get the same thing with this.

I tried something new. I have been working on a copy of my Workbook. Back when I started this, I briefly had it working, then lost it. So, I tried again, this time with a NEW copy of my workbook. It Worked! So I thought. I saved the file, closed it. Then re-opened it and it went back to not working. Any attempt to redo the code to lock a sheet, nothing. So, something happens when I close the workbook. It works initially, then once its closed, it completely fails. Even if I try to auto-lock a completely different sheet (there is a sheet for each day of the month), it wont work. Any ideas? Thanks for all the replies!

[garyreid81]

OK, I think I figured it out. Went into the Macros Security on my work computer and compared to those on my personal computer. For some reason, on my work computer, "Enable all macros..." wasn't selected and also there was no check next to "Trust access to the VBA..." I assumed (wrongly) that saving a file as "Macro Enabled Excel Workbook" would in-turn enable all macros...Guess not!

This was probably a dumb one on my part, but I've never used VBA. Thanks for all the help!