code to lock a range of cells after completing

[Cablek]

I have a code to automatically enter the date if any data is entered into cells B14 to B100

Private Sub Worksheet_change(ByVal Target As Excel.Range)
If (Target.Count = 1) And _
(Not Intersect(Target, [B14:B10000]) Is Nothing) Then _
Target.Offset(0, -1) = Date
End Sub

but now I want the user to enter date into B14, C14 and E14 THEN once completed I want to LOCK those cells
then user can come back later and enter B15,C15 and E15 right down to B100, C100 and E100

A B C&D E

Date	Qty built	PO#	built by

<colgroup><col span="3"><col span="2"></colgroup><tbody>
</tbody>

Does this make any sense?

 

[Cablek]

Hey JB,
I modified the procedure a bit...

If Column B is modified (between row 14 and 1000) enter date
If Target.Column = 2 And Target.Row >= 14 And Target.Row <= 10000 Then
Sheets("Shipping").Unprotect
Target.Offset(0, -1) = Date
End If

AFTER If column F if modified between row 14 and 1000 then lock that row A-F

If Target.Column = 6 And Target.Row >= 14 And Target.Row <= 10000 Then
Sheets("Shipping").Unprotect
Target.Offset(0, -5).Resize(1, 6).Locked = True
Sheets("Shipping").Protect
End If

so total code looks like this

Private Sub Worksheet_change(ByVal Target As Excel.Range)
If Target.Column = 2 And Target.Row >= 14 And Target.Row <= 10000 Then
Sheets("Shipping").Unprotect
Target.Offset(0, -1) = Date
End If
If Target.Column = 6 And Target.Row >= 14 And Target.Row <= 10000 Then
Sheets("Shipping").Unprotect
Target.Offset(0, -5).Resize(1, 6).Locked = True
Sheets("Shipping").Protect
End If

End Sub

1st IF works like a charm
2nd if also works but too well as it locks
ROW 14 A-F (as requested) but them it also locks
ROW 15-A-F (needed for next order)
Row 16-A,B (C is unlocked) D

it's really random but probably NOT
is there anyway to see what cells are locked when it executes?

 

[Cablek]

JB I think I have it figured out... If I run the Module4
Sub UnlockCells()


Sheets("Shipping").Range("B14:G100").Locked = False


End Sub


before entering data is seems to work GREAT... if I don't it unlock the sheet 1st it seems to be very sketchy.

Can I call up a Module from the VBA code?

 

[bellman101]

Evening,

If you use the line Range("B14:G100").locked = False, then it will work nicely. Unfortunately, it also means that it will be unlocking all the previous cells that have been locked following orders being entered, so probably not what you are after. It's only really to use at the very start, so you have a blank canvas of unlocked cells to work with.

You can't see which cells it is actually unlocking, BUT - you can see which cells it is referring to.

For example with this line:

Target.Offset(0, -5).Resize(1, 6).Locked = True

If you follow it with:

MsgBox Target.Offset(0, -5).Resize(1, 6).Address

This will give you the cell reference of the range it has just locked.

Looking at your code, there is no way for it to be locking multiple rows at the same time, so there must be something else going on.

Also, I notice this line seems to be missing from your first IF statement:

Target.Offset(0, -1).Resize(1, 2).Locked = True

meaning after editing cell B14, A14 and B14 will both still be unlocked.

 

[Cablek]

Yes I actually changed the way it way working so that it doesn't lock till you get to the "G" column... there was no use locking A + B then locking the rest.... this will give the user time to modify his inputs as long as he hasn't filled in the last field.
I found that by running the
ub UnlockCells()


Sheets("Shipping").Range("B14:G100").Locked = False


End Sub
module before typing in any data works well... after exiting then returning into Excel the previously locked cells are still locked and user can continue from where they left off.

If I run this module before "clearing contents" to keep the sheet clean then saving it is also seems to work...starting off in the right "status" seems like the issue can that be?

 

[bellman101]

Oh yes, you will need to use the Locked = False on all cells when clearing contents, or you first x rows will be locked with nothing in them.

However, you wouldn't want to call it every time. In theory it should leave no cells in B14:G100 unlocked. There's something not quite right if the previously locked cells are still locked.

I have just created a blank book and named a sheet 'Shipping'.

I copied and pasted your above code directly into the 'Shipping' code window, placed a breakpoint on line 1, and entered some random text into several rows of column G.

Everything works precisely as it should. No other rows are locked that shouldn't be.

Try it yourself. Add a new sheet called 'Test' to your workbook. Open the code window of that sheet and paste your code in exactly as it is. Manually select and unlock A14:G100 (If you're unsure, it's right click on range, format cells, protection, uncheck 'Locked'), manually protect your sheet, and then play with columns A to G. All work perfectly.

 

[bellman101]

I've just thought, you don't have any other code in that sheet do you? such as a Selection Change event? Thinking about it, I reckon this is highly likely... Let me know.