Locking cells automatically after enter

[HagridC]

Greetings,
I'm new to writing and using macros and need some help.
I have 9 worksheets in a workbook
On one of the sheets I have a grid 25 x 25
The worksheet is protected.
All cells in the grid are not locked at the present time.
What I want to do is have the cell automatically lock after data has been
entered.
I have been trying with ActiveCell.Locked=TRUE, OnKey.ENTER and other
statements in various orders with no success.
Could someone please advise.

Also, where do I put this macro, in the sheet# or in the module#

 

[Dave Peterson]

I've always thought that this kind of thing is a bad idea. I make too many
typos! So I'd be going back to the developer asking them to fix my mistake for
each one of them.

But if you want, you can use a worksheet event:

Right click on the worksheet tab that contains that 25x25 table.
Select View code
and paste this into the code window that just opened (right hand side usually)

You'll have to change the password and the address of the top left corner of
that 25x25 table:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim myIntersect As Range
Dim myPassword As String

myPassword = "hi there"

With Me 'the worksheet with the code
'25 rows by 25 columns starting in A1
Set myRng = .Range("a1").Resize(25, 25)

Set myIntersect = Intersect(Target, myRng)

If myIntersect Is Nothing Then
Exit Sub
End If

.Unprotect Password:=myPassword
For Each myCell In myIntersect.Cells
myCell.Locked = True
Next myCell
.Protect Password:=myPassword
End With

End Sub

Then back to excel to test it.

(I still think that this will cause more trouble than it's worth.)

 

[HagridC]

Hi Dave,
Thanks for the info although I could not get it to work.
I've attacked the issue from a differrent perspective ...
After the data is entered into the unlocked cells I select all of the cells
that have been changes by holding down the Ctrl key.
then ...
I created a control button for the following

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
With Selection.FormatConditions(1).Interior
.ColorIndex = 3
.Pattern = xlLightUp
End With
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

This does what I want but allows some flexibility for mistakes and changes
before locking the selected cells.
--
Have a Great Day!
HagridC

I've always thought that this kind of thing is a bad idea. I make too many
typos! So I'd be going back to the developer asking them to fix my mistake for
each one of them.

But if you want, you can use a worksheet event:

Right click on the worksheet tab that contains that 25x25 table.
Select View code
and paste this into the code window that just opened (right hand side usually)

You'll have to change the password and the address of the top left corner of
that 25x25 table:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim myIntersect As Range
Dim myPassword As String

myPassword = "hi there"

With Me 'the worksheet with the code
'25 rows by 25 columns starting in A1
Set myRng = .Range("a1").Resize(25, 25)

Set myIntersect = Intersect(Target, myRng)

If myIntersect Is Nothing Then
Exit Sub
End If

.Unprotect Password:=myPassword
For Each myCell In myIntersect.Cells
myCell.Locked = True
Next myCell
.Protect Password:=myPassword
End With

End Sub

Then back to excel to test it.

(I still think that this will cause more trouble than it's worth.)