locking a row

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 
Last edited:
Upvote 0

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
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?
 
Upvote 0

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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>
 
Upvote 0

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
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
 
Upvote 0

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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.
 
Upvote 0

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
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.
 
Upvote 0

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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>
 
Upvote 0

Forum statistics

Threads
1,190,558
Messages
5,981,688
Members
439,730
Latest member
gjvv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top