Dynamic unlock a cell (under condition)

[MyVi]

Hello guys, lets see if anyone could help me out on how to do this:

I have a sheet protected with a password, the reason of this (you know)
is that there are some cells that are locked (to not see their formulas
or 'cause I don't want them to be changed under any circustances).

This is working fine, but now I have the need of some cells (two ranges
actually, these are E7:E56 & N7:N56) that they come locked by default
but under certain conditions I would need them to get unlocked.

Taking an E7 as example, let's say that depending on what we have in B7
this E7 will change its status (locked or unlocked) and it will show
some data or not. (Then it would be the same for the rest of the
ranges). The conditions are as follow:

IF B7 = "1x.xxx" or "2x.xxx" or "3x.xxx" or EMPTY THEN E7 = LOCKED &
EMPTY
IF B7 = "5x.xxx" THEN E7 = LOCKED & "YES"
IF B7 = "4x.xxx" or "6x.xxx" THEN E7 = UNLOCKED & EMPTY

As we see (as aclaration) we can have in B7 numers of 5 digits where
first digit must start on 1 or 2 or ... up to 6. B7 can be EMPTY as
second thing (or a number or empty). Actually there can be a 0 number
as well (simuling the cell is empty though), but this one would be not
shown itself.

A small detail would be that the sheet is password protected for some
reason (you may figure it out) and I am not really interested on let
people get the password. I say this 'cause I've seen around some VBA
codes where, to perform what I need: [lock & unlock cells] they protect
& unprotect the sheet all the time using the password, and well you
just need to ALT+F11 to see the code to find out the password, and
well, as long as there is no (really) other way to do so, I would
prefer to not doing it this way instead.


Anyone knows how to perform this? VBA code? Any other way?
Thank you very much in advanced.

Victor

 

[Otto Moehrbach]

Victor
Do you know that you can password protect the code itself so no one can
view the code and see the passwords? Of course, someone determined and with
sufficient knowledge of Excel will be able to break any security scheme you
can come up with. Excel is not intended to be a secure platform.
As I see from what you say, you want some cells locked/unlocked/blank as a
function of the values in some other cells. Is that right? If so, that can
be done with VBA. My question now is: When do you want this to happen?
When the file is opened? Closed? Saved? When the user clicks a button?
When an entry is made in some specific cell or row or column or sheet?
You state a range in your post. Is that the range you want to use or was
that just an example range?
You state 3 conditions and the desired result if the condition is met. Is
that just an example or actually what you want? HTH Otto

Hello guys, lets see if anyone could help me out on how to do this:

I have a sheet protected with a password, the reason of this (you know)
is that there are some cells that are locked (to not see their formulas
or 'cause I don't want them to be changed under any circustances).

This is working fine, but now I have the need of some cells (two ranges
actually, these are E7:E56 & N7:N56) that they come locked by default
but under certain conditions I would need them to get unlocked.

Taking an E7 as example, let's say that depending on what we have in B7
this E7 will change its status (locked or unlocked) and it will show
some data or not. (Then it would be the same for the rest of the
ranges). The conditions are as follow:

IF B7 = "1x.xxx" or "2x.xxx" or "3x.xxx" or EMPTY THEN E7 = LOCKED &
EMPTY
IF B7 = "5x.xxx" THEN E7 = LOCKED & "YES"
IF B7 = "4x.xxx" or "6x.xxx" THEN E7 = UNLOCKED & EMPTY

As we see (as aclaration) we can have in B7 numers of 5 digits where
first digit must start on 1 or 2 or ... up to 6. B7 can be EMPTY as
second thing (or a number or empty). Actually there can be a 0 number
as well (simuling the cell is empty though), but this one would be not
shown itself.

A small detail would be that the sheet is password protected for some
reason (you may figure it out) and I am not really interested on let
people get the password. I say this 'cause I've seen around some VBA
codes where, to perform what I need: [lock & unlock cells] they protect
& unprotect the sheet all the time using the password, and well you
just need to ALT+F11 to see the code to find out the password, and
well, as long as there is no (really) other way to do so, I would
prefer to not doing it this way instead.


Anyone knows how to perform this? VBA code? Any other way?
Thank you very much in advanced.

Victor

 

[MyVi]

Hi Otto.

First of all, thank you for answering.

Otto, what I mean on seeing the passwords is:

Once you have password protected your sheet, when you click ALT+F11 you
access the VBA code. Then, if you put some VBA code to
unprotect/protect your sheet again specifing the password, then, some
people could simple do that and to find out what the password is. But
this is not very important, 'cause it is not really a big deal if
someone find out about the password. The main idea is just to make the
worksheet work properly.

Yes, Otto, what I need (or want... whatever) is that some one, on what
he/she puts on a single CELL (let's say B7, but you have a whole group
of them, where any of these cells must do the same action where B7 is
for E7 and B8 is for E8.. and like this) a number (5 digits number) and
a VBA code analice what number you have introduced and then something
happend to another CELL (this case, exemple is E7). The "E7" (as our
exemple) is blocked by default, but depending on the 3 conditions I've
shown you before the "E7" gets unblocked or not. We shall take into
consideration that what it is analiced on B7 (the cell) is the first
digit of the number you introduce.

IF B7 = "1x.xxx" or "2x.xxx" or "3x.xxx" or EMPTY THEN E7 = LOCKED &
EMPTY
IF B7 = "5x.xxx" THEN E7 = LOCKED & "YES"
IF B7 = "4x.xxx" or "6x.xxx" THEN E7 = UNLOCKED & EMPTY

Another small detail would be that these cells (so B7 for the exemple)
are never really empty. When I say so, I just mean they are wonna be
visual empty but there's gonna be a "0" on it and any time you erase
the number you have introduced in "B7" the empty "thing" is going to be
reemplaced by a "0" number.

Then answering to your questions.....

you want some cells locked/unlocked/blank as a function of the values in some other cells. Is that right?

That's right. This is what I want. The cells I want to be changed are
locked(by default)/unlocked/blank and I want to be changed depending on
some values in other cells (1 value in a cell makes change into another
cell).

If so, that can be done with VBA.

Well, yes, I think this can be with VBA. I just wonder how 'cause I am
not a programer.

When do you want this to happen? When the file is opened? Closed? Saved? When the user clicks a button? When an entry is made in some specific cell or row or column or sheet?

I want this hapend whenever I just put a number in a cell. Again, "B7"
as exemple. By default "B7"=0 (or visual empty) and "E7" is blocked.
Then you change the "0" of B7 by a 5 digits' number, lets say 23456.
Then I need "E7" to change once I leave the cell B7 (for instance when
I clic into another cell, let's say.. when I clic TAB?)

You state a range in your post. Is that the range you want to use or was that just an example range?

The range I've post is the reall range.

Cells to change from "0" to any 5 digitis' number = B7:B56 & K7:K56.
Cells by default blocked, that have to change depending on what the
previous cells have on them = E7:E56 & N7:N56

Where:

E7 changes when ever B7 change its number
E8 changes when ever B8 change its number
..
..
E56 changes when ever B56 change its number

&
N7 changes when ever K7 change its number
N8 changes when ever K8 change its number
..
..
N56 changes when ever N56 change its number.

You state 3 conditions and the desired result if the condition is met. Is that just an example or actually what you want?

The 3 conditions are the real ones. Actually what I need to happend.

ie.(and I say exemple 'cause this is one of the possibilities, but is
for real)
If cell B7 is equal to 14876 then E7 will be locked & Empty (like it is
by default)
If cell B7 is equal to 52456 then E7 will be unlocked and showing YES
on it.

and I following the 3 conditions are shown above.


Ok, I hope you have enough information to help me out.

Thank you so much

 

[MyVi]

Hello people, so far I'm trying with thise code, but something seems to
be wrong 'cause still does not work.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b7:b56,k7:k56")) Is Nothing Then
ActiveCell.Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("e7:e56,n7:n56")) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Target.Offset(, 3).ClearContents: Exit Sub
With Target.Offset(, 3)
Select Case Left(Target, 1)
Case 1, 2, 3: .Locked = True: .ClearContents
Case 4, 6: .Locked = False: .ClearContents
Case 5: .Locked = True: .Value = "SI"
End Select
End With

End Sub


&


Private Sub Workbook_Open()
Worksheets("Sheet3").Protect Password:="aBc", UserInterfaceOnly:=True
End Sub


ANY IDEA?

Thank you


Victor

 

[Dave Peterson]

What did you want to do with the first _selectionchange event. I'm not sure it
does what you want.

And the second routine. You'll want to stop any changes you make from calling
this routine over (and over and over)... You can do that by controlling
"application.enableevents".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myPWD As String

myPWD = "hi"

If Intersect(Target, Me.Range("e7:e56,n7:n56")) Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:=myPWD
Application.EnableEvents = False
If IsEmpty(Target.Value) Then
Target.Offset(, 3).ClearContents
Else
With Target.Offset(, 3)
Select Case Left(Target.Value, 1)
Case "1", "2", "3": .Locked = True: .ClearContents
Case "4", "6": .Locked = False: .ClearContents
Case "5": .Locked = True: .Value = "SI"
End Select
End With
End If
Application.EnableEvents = True
Me.Unprotect Password:=myPWD

End Sub


And Left() returns a string. I changed 1,2,3 to "1","2","3". Excel/VBA didn't
seem to care, but I do <bg>.

 

[MyVi]

OKEY, Here is it how I've done it.

under ThisWorkbook this code:

Private Sub Workbook_Open() 'proteje y desproteje la hoja
Worksheets("Sheet3").Protect Password:="aBc", UserInterfaceOnly:=True
End Sub


under the sheet, this other code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'impide
seleccionar mas de 1 celda
If Not Intersect(Target, Range("b7:b56,k7:k56")) Is Nothing Then
ActiveCell.Select
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 2 Then 'celdas NUNCA vacias, sino con un 0
If Target = "" Then Target = 0


Else
If Target.Column = 11 Then
If Target = "" Then Target = 0
Else


End If
End If


If Intersect(Target, Range("b7:b56,k7:k56")) Is Nothing Then Exit
Sub
If IsEmpty(Target) Then Target.Offset(, 3).ClearContents: Exit Sub
With Target.Offset(, 3) 'bloqueda/desbloquea las celdas segun las 3
condiciones
Select Case Left(Target, 1)
Case 1, 2, 3: .Locked = True: .ClearContents
Case 4, 6: .Locked = False: .ClearContents
Case 5: .Locked = True: .Value = "SI"
Case 0: .Locked = True: .ClearContents 'esta condicion deja
celda en estado original

End Select
End With
End Sub




It does not seem 100% stable though but it works fine with all the
other stuff I have under my worksheet.

Thank you very much to all of you for your support.

Victor