Solved: Disable any changes to tha value in the cell.

[kbsudhir]

Hi All,

I want to know the way to lock the cell so that no changes can be made once the macro enters the value in that cell.


Please provide a way to do this.


Sudhir.
:help

[Simon Lloyd]

You have to lock the cell then protect the worksheet!

[Bob Phillips]

Be carefule, by default all cells are locked, so you need to unlock any that you don't want locked.

[PaSha]

try using code like..

Worksheets("NameOfYourSheet").Unprotect Password :="YourDesiredPassword"

' some code you want to be executed

Worksheets("NameOfYourSheet").Protect Password :="YourDesiredPassword"

but you need to first lock your sheet for preventing any changes from others by clicking on the tab of the sheet and adding protection...

but this type of code - protects all your sheet not just the specific cell...

[Digita]

Try the following codes:


Option Explicit
Dim PrevVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Len(PrevVal) > 0 Then
Target.Formula = PrevVal
Else
Target = Target.Formula
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Not IsEmpty(Target) Then 'Assign previous value or formula
If Target.HasFormula Then PrevVal = Target.Formula
If Not Target.HasFormula Then PrevVal = Target.Value
Else
PrevVal = vbNullString
End If
End Sub


How to copy the codes:

Activate the sheet you want to protect the cell contents,
Right click on the sheet tab,
Copy the above codes to the sheet module & close VBE.

You are then in business.

HTH

cordially


kp

[mikerickson]

I like to set the UserInterfaceOnly property to True.


ActiveSheet.Protect UserInterfaceOnly:=True
makes it so the sheet can be changed by VB code, but the user can't alter things with the keyboard or mouse. Changing protection on the sheet will not change the UserInterfaceOnly unless it is secificaly mentioned. Closing the workbook sets it to False.

[kbsudhir]

Thats great. Thank you all of u for ur time.

I really learned a lot.

But I think I can't lock just few cells and let the remnaining cells unlocked.

Am I Right in this case......??????

Thanks all of you.


:bow: :bow:

[Bob Phillips]

Yes you can, as long as you are happy that all of those cells are changeable.

[Simon Lloyd]

you can use this to lock certain cells:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'cells to lock once filled
'If Not Intersect(Target, Range("A1,C1,E1")) Is Nothing Then'use this for non contiguous range
If Target.Value = "" Then
Exit Sub
Else
Target.Cells.Locked = True
End If
End Sub
Remember cells will not be locked until the sheet is protected.