Locking / Unlocking Cell with out Protection/ Unprotection of Shee
in [Programming]
|
Prev: Select Worksheets, Copy to New Workbook
Next: How to get access 2003 data table particular data in excel.
From: Premanand Sethuraman on 18 Jul 2008 08:49 Dear all, Presently, I am doing a VB Coding for a Worksheet in which I want to control the sheet. I just want to know if there is any possibility of locking or unlocking the cells with out Unprotect/Protect Commands. For example, In Cell A1, if I select "Boiler"(from drop down list), then Cells A2,A3 and A4 should be locked so that user cannot write anything over there. Suppose if I select "Kiln",in A1, immediately the cells A2,A3,A4 should be unlocked. The problem I am facing is like this.. Private Sub Worksheet_Change(ByVal h1 As Range) Application.ScreenUpdating = False If h1.Row = 12 And h1.Column <= 7 Then If h1.Value = "Boiler" Then Range(Cells(13, h1.Column), Cells(15, h1.Column)).Locked = True Else Range(Cells(13, h1.Column), Cells(15, h1.Column)).Locked = False End If End If My program is protected already by default and the password is around 45 characters. Hence I do not wanrt to put the protect / unprotect command in the above coding. Is it possible to use any application events for performing the above coding successfully. Regards, Premanand S
From: John Bundy on 18 Jul 2008 08:58
You could get away with capturing any data entered into the cells and delete it, but that seems silly. Why don't you protect and unprotect? You say because you have a large password, you pass the password in through code to protect and unprotect, no need to type it. All password activity should be done in code except when you want it manually entered. -- -John Please rate when your question is answered to help us and others know what is helpful. "Premanand Sethuraman" wrote: > Dear all, > Presently, I am doing a VB Coding for a Worksheet in which I want to control > the sheet. I just want to know if there is any possibility of locking or > unlocking the cells with out Unprotect/Protect Commands. > For example, In Cell A1, if I select "Boiler"(from drop down list), then > Cells A2,A3 and A4 should be locked so that user cannot write anything over > there. Suppose if I select "Kiln",in A1, immediately the cells A2,A3,A4 > should be unlocked. The problem I am facing is like this.. > > Private Sub Worksheet_Change(ByVal h1 As Range) > Application.ScreenUpdating = False > If h1.Row = 12 And h1.Column <= 7 Then > If h1.Value = "Boiler" Then > Range(Cells(13, h1.Column), Cells(15, h1.Column)).Locked = True > Else > Range(Cells(13, h1.Column), Cells(15, h1.Column)).Locked = False > End If > End If > > My program is protected already by default and the password is around 45 > characters. Hence I do not wanrt to put the protect / unprotect command in > the above coding. Is it possible to use any application events for performing > the above coding successfully. > > Regards, > Premanand S |