Ad-Free CAclubindia and Daily Updates on your mobile. Subscribe Now!

Disable cell editing once the data entered

Yuvraj Kumar (Asst. Manager (IT)) (27 Points)

23 September 2008

Respected Members,

I am developing a custom software in Microsoft Excel in 97-2003 Format. This program will determine the total and maximum running vehicle through graph. The program is completed but the thing is that, I wanted to disable a particular cell as soon as the data is entered in cell and pressed enter, means to stop the users to edit the value as soon as they press enter. So, can anyone just help me out, to solve out this querry? Any help, ideas and suggestions will be appreciated. Hope the best from your side.

Thanks in advance,

Regards,

Yuvraj Kumar, Asst. Manager (Information Technology)

Reply
Follow
Share

3 Replies

subash (CA Final Student) (110 Points)
Replied 23 September 2008

Try this Macro in the sheet where data is entered

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo MyError

Selection.Locked = True

Selection.FormulaHidden = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Exit Sub

MyError:

MsgBox ("i found a culprit") ' in case somebody tries to delete the range using control+"-" then msgbox gets poppedup

End

End Sub

Attached File : 5 ae.txt downloaded: 228 times

Upgrade to CAclubindia Pro. Subscribe Now!

Yuvraj Kumar (Asst. Manager (IT)) (27 Points)
Replied 25 September 2008

Dear,

First of all thank you very much for the reply. I had attached the said file, please download it and in the column "I" which is "End, Odometer End", i want to apply restriction for the user as soon as he enters the data, and presses enter. He should not be able to delete/edit the data once entered. Waiting for a good resolution.

Thank you in advance!

Attached File : 37 kilometer run of vehicles.xls downloaded: 221 times