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

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

Dear John,
Thanx for your reply. The reason for a big password is that nowadays many
password crackers are available. Hence as it is an important spread sheet, i
want to give characters of upto 43.Because even though if they use password
cracker, it will take at least 15 - 20 minutes for unlocking the password. It
will be difficult for the person to sit and remove even thro cracker as it
will consume more time.

So you are suggesting me to put the password (Activesheet.protect
Password:="wfewewfwefewwe") in the coding and it is not possible to
lock/unlock the cells with out the above command.


Prem.

"John Bundy" wrote:

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