• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA target cells values to lock / unlock dependent cells

G

GioP

New Member
I have the following VBA to build for a worksheet which by default is protected.

For the following event VBA should be looking for values in specific cells and unprotect related cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Case 1

If cell “U10” is 0 then cells “B19”, “B21”, “B23”, “B25”, “B27” and “R19”, “R21”, “R23”, “R25”, “R27”

Should be locked.


Case 2

If cell “U10” > 0 then cells “B19”, and “R19”, should be unlocked

cells “B21”, “B23”, “B25”, “B27” and “R21”, “R23”, “R25”, “R27” should remain locked


Case 3

If cell “U10” > 0

then

cells “B19”, and “R19”, should be unlocked

then

if cell “R19” value <1

then

cells “B21”, and “R21”, should be unlocked

cells “B23”, “B25”, “B27” and “R23”, “R25”, “R27” should remain locked


Case 4

If cell “U10” > 0

then

cells “B19”, and “R19”, should be unlocked

then

if cell “R19” value <1 and cell value “R21” <1 and cell “R21” value + cell “R19”value <1

then

cells “B23”, and “R23”, should be unlocked

cells “B25”, “B27” and “R25”, “R27” should remain locked


etc


I know “target” and/or “intersect” and/or “case” functions should be used, but unfortunately did not manage to get result.


Hoping to find swift support in this matter. Any help is highly appreciated


Thanks and regards
 
I think this should help get you started. Note that I switched to using a Change_event macro, as it seemed the more likely to be used.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect

Select Case [u10].Value
Case 0
[B19, B21, B23, B25, B27, R19, R21, R23, R25, R27].Locked = True
Case Is > 0
[B19, R19].Locked = False
[B21, B23, B25, B27, R21, R23, R25, R27].Locked = True
If [R19] < 1 And [R21] < 1 And [R21] + [R19] < 1 Then
[B23, R23].Locked = False
[B25, B27, R25, R27].Locked = True
ElseIf [R19] < 1 Then
[B21, R21].Locked = False
[B23, B25, B27, R23, R25, R27].Locked = True
End If
Case Else
'u10 is less than 0
End Select
Me.Protect

End Sub
[/pre]
 
Thanks a lot. Still getting problem on line

[B19, B21, B23, B25, B27, R19, R21, R23, R25, R27].Locked = True


Do you know what makes it to fail?


Thanks again
 
Hmm. Can you elaborate on what the problem is? Does the code lock up, or it just isn't doing the right thing? Are merged cells involved?
 
Hi there


I had some cells merged. This was probably the reason macro to look up on above range.


Looks it works now. Good base to build my case.


Thanks a lot for your help


Appriciated :)
 
Phew, glad you were able to figure it out. Let us know if you need any more help!
 
Hi there


Thanks for support and help.

I rushed to celebrate, the code is partly working... Probably it requires minor adjustment in VBA to make it right. Tried hard but no result...

Unfortunately the code stops functioning for raw 23. In other words when following condition is true


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R21] + [R19] < 1 Then


In fact cells B23 and R23 are remaining locked


To test the code uploaded worksheet you can find at the following address:

https://docs.google.com/open?id=0B4mitJ-SIt9bMHB6OXZNdEgwdkk


Below find the complete code:


Private Sub Worksheet_Change(ByVal Target As Range)

Me.Unprotect


Select Case [u11].Value

Case 0

[B19, B21, B23, B25, B27, R19, R21, R23, R25, R27].Locked = True

Case Is > 0

[B19:C19, R19].Locked = False

[B21, B23, B25, B27, R21, R23, R25, R27].Locked = True


If [R19] = 1 Then

[B21, B23, B25, B27, R21, R23, R25, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 Then

[B19, B21, R19, R21].Locked = False

[B23, B25, B27, R23, R25, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R21] + [R19] = 1 Then

[B19, B21, R19, R21].Locked = False

[B23, B25, B27, R23, R25, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R21] + [R19] < 1 Then

[B19, B21, B23, R19, R21, R23].Locked = False

[B25, B27, R25, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R23] > 0 And [R23] < 1 And [R21] + [R19] + [R23] = 1 Then

[B21, B23, R23, R21].Locked = False

[B25, B27, R25, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R23] > 0 And [R23] < 1 And [R21] + [R19] + [R23] = 1 Then

[B21, B25, B23, R19, R21, R23, R25].Locked = False

[B27, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R23] > 0 And [R23] < 1 And [R25] < 1 And [R25] > 0 And [R21] + [R19] + [R23] + [R25] = 1 Then

[B21, B23, B25, R19, R21, R23, R25].Locked = False

[B27, R27].Locked = True


ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R23] > 0 And [R23] < 1 And [R25] < 1 And [R25] > 0 And [R21] + [R19] + [R23] + [R25] < 1 Then

[B21, B23, B25, B27, R19, R21, R23, R25, R27].Locked = False


End If


Case Else

'U11 is less than 0

End Select


Me.Protect


End Sub


Any help on the way? Thanks agin :)
 
Hi, GioP!


Just passing by and read the topic.


I have neither tried the code nor checked the uploaded file, but at a first glance Luke M's code seems to be quite more legible than yours. This doesn't intend to be a hard critic, it's only to point out that when using Select Case constructions it's highly recommendable to keep each snippet for each Case condition as clear as possible and clear doesn't mean short, just understandable.


Give a look to your Case > 0 code, after the first 2 lines, there's a very complex If structure, which I suggest you to redefine following another approach like the technique of decision matrix. Here's the link:

http://en.wikipedia.org/wiki/Decision_matrix

and perhaps change the analysis from "if x=condition then y=value1 else y=value2" to something like "y=value1, when x=condition, z=condition2, ..." would help building code cleaner and simple.


Regards!
 
Hi Sir


I will follow your advice. In advance not sure if I will manage, but as an optimist person will try. Programming is not my field, but have to make it because have presentation on Monday... and above code is part of it


Thanks and if no result will wait helppppppp we all need in this life :)
 
Hi, GioP!

If you don't succeed on a cleaner result of the un/locking conditions, just come back and someone will surely try to help you.

Despite of this and regarding your intention and predisposition, if I start from Luke M's code, what is exactly the issue that you found?

Regards!
 
What to say whole issue is that I do not see why the code is not functioning. It is not giving any error massage. Even though code is not written in advance manner in principal should be working…

Problem is that when R19 + R21 < 1 cells B23 and R23 should be unlocked. Here is the problem in this line

ElseIf [R19] > 0 And [R19] < 1 And [R21] > 0 And [R21] < 1 And [R21] + [R19] < 1 Then


Any idea? Shortcut to resolve?


Thanks and cheers
 
Hi, GioP!


Analyzing your definitions of first post:

a) B19, B21, B23, B25, B27, R19, R21, R23, R25 and R27 cells are locked (starting condition)

b) U10>0 unlocks cells B19 and R19

c) R19<1 unlocks cells B21 and R21

d) R19<1 and R21<1 unlocks cells R23 and R23

e) cells B25, B27, R25 and R27 will be always locked

f) what to do when U10<0? (actually nothing, just confirmation)


Is that the logic behind? Am I wrong? If not, we're almost done; if yes, please elaborate over this previous schema, please don't again your last code since it's difficult for me to understand it. Thank you.


Regards!


EDIT:


What if inputted in an unlocked cell and then inputting in another cell that changes lock condition of the first one? What to do with it's value?
 
Hi, GioP!


I think I got it. Analyzing your definitions of second post the updated status is this?

a) B19, B21, B23, B25, B27, R19, R21, R23, R25 and R27 cells are locked (starting condition)

b) U10>0 unlocks cells B19 and R19

c) R19<1 unlocks cells B21 and R21

d) R19+R21<1 unlocks cells R23 and R23

e) R19+R21+R23<1 unlocks cells R25 and R25

d) R19+R21+R23+R25<1 unlocks cells R27 and R27

e) SUM(R19:R27) shouldn't in any case be >1

Please confirm or rectify over this schema.


Regards!
 
Hi, GioP!


A little of lateral thinking... go to your first post, to your original code... try changing "U10" by "U11"...


Regards!


PS: magic? no, just checked your uploaded file

PS2: don't say anything... or it'll be used against you!
 
Hi Sir :)


In fact I have changed U10 by U11 prior uploading.


When you have time please have a look into the original worksheet. You may see that conditional formatting is working, however cells B23 and R23 remain protected.

So U10 and U11 are out of picture.


PS1: Like in chess. Your move

PS2: Ref to PS above no magic yet :)


Regards
 
Hi, GioP!


Like in simultaneous chess, your move too. Please answer this comment:

http://chandoo.org/forums/topic/vba-target-cells-values-to-lock-unlock-dependent-cells#post-36864


See you tomorrow.


Regards!
 
Hi Sir


I confirm you are absolutely right in this schema with minor change for ending condition :)


SUM(R19:R27) should always = 1. (when R19, R21, R23, R25 and R27 are unlocked sum of all mentioned cells always equals 1 or in other words 100%)


a) B19, B21, B23, B25, B27, R19, R21, R23, R25 and R27 cells are locked (starting condition)

b) U11>0 unlocks cells B19 and R19

c) R19<1 unlocks cells B21 and R21

d) R19+R21<1 unlocks cells R23 and R23

e) R19+R21+R23<1 unlocks cells R25 and R25

d) R19+R21+R23+R25<1 unlocks cells R27 and R27

e) SUM(R19:R27) should be equal to 1 always


Confirmed


My regards and thanks Sir


PS


Will your next move bring check mat on this code? :)Your move please
 
Hi, GioP!


Try replacing your whole worksheet's code by this:

-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
' declarations
' start
Me.Unprotect
Application.EnableEvents = False
' process
[B19, B21, B23, B25, B27, R19, R21, R23, R25, R27].Locked = True
If [U11].Value > 0 Then
[B19, R19].Locked = False
If [R19].Value < 1 And [R19].Value > 0 Then
[B21, R21].Locked = False
If [R19].Value + [R21].Value < 1 And [R21].Value > 0 Then
[B23, R23].Locked = False
If [R19].Value + [R21].Value + [R23].Value < 1 And [R23].Value > 0 Then
[B25, R25].Locked = False
If [R19].Value + [R21].Value + [R23].Value + [R25].Value < 1 And [R25].Value > 0 Then
[B27, R27].Locked = False
End If
End If
End If
End If
Else
[B19, B21, B23, B25, B27, R19, R21, R23, R25, R27].Value = ""
End If
' end
Application.EnableEvents = True
Me.Protect
End Sub
[/pre]
-----


At least check, don't know if checkmate. Your move.


Regards!


PS: if checkmate please pick up the pieces and the board and store them properly (i.e., adjust the damned conditional formatting conditions for row 19 :)
 
@Luke M

Hi!

Thank you for your comments, but let us wait for GioP's move. You know he/she's very able to mess up the code in such strange ways that perhaps... who knows? :p

Remember that the opponent King's still stand up and didn't surrender, so...

Regards!
 
@Debraj Roy

Hi!

Oops! didn't know GioP was a Master... if I did I should've been more careful while playing and surely not simultaneous.

http://en.wikipedia.org/wiki/Blunder_%28chess%29

Regards!
 
DONE


Sorry being late guys to reply, but I’m sure it is never late to admit that the code is perfect.

King is down - Check Mate.


SIR your code works perfectly well so for me your status from Sir has changed to IT GOD. Ok will change my statement little bit. In my opinion IT GOD is top in hierarchy so for now it is IT Archangel and respectively Luke M’s is IT Angel.


Still to go and to achieve IT GOD status I will keep right to call another game in the future. (I believe already you know me well that I’m able to bring messy codes for solutions. In other words still I need to prove that I’m master to mass up codes


Will have a beer tonight to start planning another thread…


Cheers


P.S.

Seriously thanks a lot for your time and help. Appreciated


GioP
 
Hi, GioP!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

PS: That beer is Carlsberg, if you don't mind.

PS2: You don't have to prove tautologies, they're evident by themselves

PS3: I always talk seriously.

PS4: Didn't say anything about writing.
 
You must log in or register to reply here.
Back
Top