locking a row

[master_of_none]

I think this has been answered before, but I can't find it. I would like to lock a row when they double click in the W column. I figured out by examples to make vba code that when they double click in a cell to create the word "locked". Now for the hard part. How to make the row locked when doubled clicked the cell. I want to protect parts of the sheet already. The first column and the first row. If someone can point me in the right direction, that would be wounderful. Thanks again.

 

[Smitty]

See if this does what you want:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">Set</SPAN> rng = Columns("W:W")<br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                    .Unprotect "yada"<br>                        Target.EntireRow.Locked = <SPAN style="color:#00007F">True</SPAN><br>                    .Protect "yada"<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You'll need to unlock all of the sheet cells first, then lock Row 1 & column A, as well as column W before trying it.

Note that I'm not sure how effective this'll be, as your users might double-click, but why do they have reason to do so, knowing that they'll then lock themselves out? You might want to think about another event.

HTH,

 

[master_of_none]

I'm trying to lock a row when a user inputs the data, so it can't be change unless you have the password. I still need to input data on the next row then lock that row, and continue to do this. Any ideas?

 

[Smitty]

Why not use a change event instead? That way after a user enters data in W it will lock seamlessly and they don't have to double-click.

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">Set</SPAN> rng = Columns("W:W")<br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                .Unprotect "yada"<br>                    Target.EntireRow.Locked = <SPAN style="color:#00007F">True</SPAN><br>                .Protect "yada"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[master_of_none]

When I do this, it locks the entire sheet. I just need it to lock that row and allow the the following rows writable until the row is locked. Or maybe I'm doing something wrong?

thanks,
Rob

 

[Smitty]

You'll need to unlock all of the sheet cells first, then lock Row 1 & column A, as well as column W before trying it.

If you haven't unlocked the cells before trying the code you'll need to comment it out, unprotect the sheet and unlock the cells where you want to allow entry, then give it a go.

 

[master_of_none]

Thanks, It works. How can I put in a message before it locks "Are you sure you want to lock this row?" If yes, lock the row. If no, don't lock the row.

 

[Smitty]

You can add a Message Box:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> AreYouSure <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        <SPAN style="color:#00007F">Set</SPAN> rng = Columns("W:W")<br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <br>            AreYouSure = MsgBox("Are you sure you want to lock this row? There's no going back from here...", _<br>                vbYesNoCancel + vbQuestion, "Lock this row?")<br><br>            <SPAN style="color:#00007F">If</SPAN> AreYouSure = 6 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'   True</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                    .Unprotect "yada"<br>                        Target.EntireRow.Locked = <SPAN style="color:#00007F">True</SPAN><br>                    .Protect "yada"<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            Else: <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>