Locking / Unlocking Cell with out Protection/ Unprotection of Shee

[Premanand Sethuraman]

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

 

[John Bundy]

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.

 

[Premanand Sethuraman]

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.

 

[paulgu]

Hi Premanand,

There are 2 things you can do:
1) Use the UserInterfaceOnly option when you protect the worksheet (note that you must do this programmatically in the workbook open event as when the workbook is closed and opened the option is lost). It means that you can make changes using code that the user can't make without unprotecting the sheet.
2) Password protect your code - you can do this in the VBA Project properties. If you really want to keep the protection password secret and you're using it in the code you must do this.
Paul