PDA

View Full Version : Conditional locking of row



sandipmulay
07-20-2010, 05:30 AM
Dear Brother,
I am trying to lock entire row of my worksheet based on content of a cell.
e.g. I want to lock row 1 based on entered value "YES" in cell AP1. and the same formula get applied to AP column.

If anybody have the solution please provide me.

p45cal
07-20-2010, 07:44 AM
in the sheet's code module (right-click its tab and choose view code, and where the cursor is flashing) paste this code:Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect userinterfaceonly:=True
Rows(1).Locked = (UCase(Range("AP1").Value) = "YES")
Columns("AP").Locked = (UCase(Range("AP1").Value) = "YES")
Range("AP1").Locked = False
End Sub
This presumes, that although AP1 is both in row 1 and in column AP you never want AP1 itself to be locked.

sandipmulay
07-20-2010, 10:26 PM
Dear p45cal,
:hi: :hi:
thanks for your quick response,
i tried for above code, but only column AP gets lock, but the row remains unlocked.
my problem definition is-
i want to lock row 1 when the cell AP1 contains YES.
Dear please give me the code.

p45cal
07-21-2010, 12:08 AM
That is very unusual! I tested this, when writing and again just now. When cell AP1 has "Yes" in it (regardless of upper/lower case) both column AP and Row 1 are locked. You are testing row 1 at the very top of the sheet? Perhaps you have some other code unlocking the cells? Or some code which deletes row 1 of the sheet, so that row 1 is not the same row 1 that was locked.
You're not just testing the cell AP1 itself are you? This cell I deliberately kept unlocked so that that single cell could be changed, otherwise, once "Yes" had been entered, you would never be able to change to 'no' without unprotecting the sheet. Is that what you want?
Normally, when I post code, if I haven't tested it I say 'untested'. On this occasion I had tested it. Have you copied it properly? What version of Excel are you using?
Perhaps attach a copy of the/a workbook where this does not work.

p45cal
07-21-2010, 12:50 AM
…or has there been some misunderstanding of requirements and a little misdirection?
You say:

my problem definition is-
i want to lock row 1 when the cell AP1 contains YES.
Dear please give me the code.but do you also want, as examples, row 6 to be locked when AP6 contains Yes, and row 32 to be locked when AP32 contains Yes? And column AP never to be locked?

In which case:Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect userinterfaceonly:=True
Set xx = Intersect(Target, Columns("AP"))
If Not xx Is Nothing Then
For Each cll In xx.Cells
Rows(cll.Row).Locked = (UCase(cll.Value) = "YES")
cll.Locked = False
Next cll
End If
End Sub

Aussiebear
07-21-2010, 05:16 AM
I believe the OP is asking for any cell in the column AP which contains the value "Yes" to enable that row to be locked.

sandipmulay
07-22-2010, 12:06 AM
Problem Definition:

I want to lock entire row when AP cell in that row contains YES.
e.g.
if Cell AP1 contains YES, entire row 1 gets lock,
if cell AP2 contains YES , entire row 2 gets lock,
If cell AP3 contains YES, entire row 3 gets lock,
and so on....

I am uning MS office EXCEL 2007.
If you have solution for above please give me the code. Request you also tell me how to enter this code in excel sheet.

waiting for answer,: pray2:

p45cal
07-22-2010, 12:43 AM
waiting for answer,: pray2:No need to wait, I already gave it in message #5, and method of entry in message #2.

sandipmulay
07-22-2010, 01:39 AM
I have tried the code given in #5, but it is not working,

I write YES in Cell AP1, then the row 1 gets lock, but also all sheet gets lock except cell AP1.

p45cal
07-22-2010, 02:06 AM
I had assumed from message #2 ("i tried for above code, but only column AP gets lock, but the row remains unlocked.") that you had previously unlocked all the cells on the sheet. This seems not to be the case.
So:
1. Manually unproect the sheet.
2. Select either all the cells on the sheet, or just the area you're likely to use.
3. Menus: Format|Cells…, Protection tab, uncheck the Locked checkbox, OK.

Try again.

sandipmulay
07-22-2010, 02:28 AM
I had tried, but till the same problem remains as stated on above message.
My problem definitions is that-

I want for entire worksheet,

when I enter YES in Cell AP1, the Row 1 Should gets lock, AP column remains unlock.

when I enter YES in Cell AP2, the Row 2 should gets lock, AP Column remains unlock.
.
.
.
.
when I enter YES in cell AP68, the Row 68 Should gets lock, AP column remains unlock.

..
.
.
and so on.

please give me code for this conditions.

p45cal
07-22-2010, 02:43 AM
I have tested the code and it works as required here.

Post the file here, or if it's too sensitive then either:
prepare a simple file without data which still fails to work and post that,
or:
if you're prepared to send it to me, send me a Private Message and I will supply an email address.

If you can do none of the above, I'm at a loss.

sandipmulay
07-22-2010, 03:02 AM
I think, i am making mistake while entering the code in sheet.
request you please give me step wise details to enter this code in the sheet.

p45cal
07-22-2010, 06:19 AM
I think, i am making mistake while entering the code in sheet.
request you please give me step wise details to enter this code in the sheet.No, that sounds like to much hard work for me. If you send the file to me I can usually spot the problem much quicker. All instructions that I would include are in previous messages anyway.