i´m developing a excel file that will be filled while whatching a soccer
game. Sometimes, lots of data must be inserted in small colums/rows in just a
few seconds, and i dont want to overwrite data that i had inserted before by
mistake. I´d like that excel blocks a cell that was modified (data was
inserted). With "block cell" i mean that if I click that cell again i wont be
able to modify the information that i had inserted before. Is that possible?
I really dont know much about macros (just learned), so please teach it for a
new user!

Say you wanted to restrict this to Sheet 1 Range A1:C20, then the
following code placed into the Sheet1 code module will protect a cell
if it is in that range and you have just entered into it a new value.
If you really need to change that cells value you would have to remove
the sheet protection first.
Before the code will work as expected you will have to unlock all the
cells on that sheet. Select the entire sheet by clicking on the grey
space above row 1's row number and just left of column A's column
label, then go FormatCellsProtection de-select Locked.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 3 Or Target.Row 20 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

To get the code into place..

1. Copy it
2. Right click the Sheet's sheet tab then select "View Code" from the
popup.
3. Paste the code
4. Return to the worksheet by either Alt + F11 or going File"Close and
return to Microsoft Excel"

If you use this method your Security level will have to be Medium.
ToolsMacroSecurityMedium. Also, everytime you open the workbook you
must click "Enable Macros" on the "Security Warning" dialog

Ken Johnson

Thanks a lot ken! that was perfect. This forum is very usefull..



"Ken Johnson" escreveu:

Say you wanted to restrict this to Sheet 1 Range A1:C20, then the
following code placed into the Sheet1 code module will protect a cell
if it is in that range and you have just entered into it a new value.
If you really need to change that cells value you would have to remove
the sheet protection first.
Before the code will work as expected you will have to unlock all the
cells on that sheet. Select the entire sheet by clicking on the grey
space above row 1's row number and just left of column A's column
label, then go FormatCellsProtection de-select Locked.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 3 Or Target.Row 20 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

To get the code into place..

1. Copy it
2. Right click the Sheet's sheet tab then select "View Code" from the
popup.
3. Paste the code
4. Return to the worksheet by either Alt + F11 or going File"Close and
return to Microsoft Excel"

If you use this method your Security level will have to be Medium.
ToolsMacroSecurityMedium. Also, everytime you open the workbook you
must click "Enable Macros" on the "Security Warning" dialog

Ken Johnson

Hi Fecozisk,

You're welcome.
Thanks for the feedback.

Ken Johnson