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
[TABLE="width: 395"]
<colgroup><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 5"][/TD]
[/TR]
[TR]
[TD]
Date[/TD]
[TD]Qty built[/TD]
[TD="colspan: 2"]PO#[/TD]
[TD]built by[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Does this make any sense?

 

[bellman101]

Ok. 1 of 2 things then. Either unprotect is not working correctly, or target cell is merged with other(s).
Try replacing 'Me.' With 'Activesheet.' (Both instances) and also check that D14 and C14 are not merged with any other cells
These are only 2 reasons for this error I think.

 

[bellman101]

Hey BJ... I truely appreciate your help

Oh, and it's 'JB'. 'BJ' means something else I think!!

 

[Cablek]

You are a genius!!! multiple cells were merged so I deleted them and expanded the fields and now as long as I enter info into the B column it locks it up perfectly
NOW
when I go into the C,D and E colums it tells me cells on the same line (eg B14 (I enter data) C14, D14 and E14 ) the cells are locked.. I need to be able to fill those fields and lock them also or lock the row once info is entered
Would a screen shot help?

 

[bellman101]

I think so!

 

[Cablek]

It won't let me post a screen shot here.... if I give you my email address I can send it to you that way... is that ok?
[TABLE="width: 1059"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[bellman101]

I don't think that will be necessary. So you are saying when you update b14, A14 and B14 should lock. C, D and E should remain unlocked. When any of them are updated it then locks just that cell? Is this right?

 

[Cablek]

no... when you update B14 it automatically adds the date into A14 but if you move over to cell C14 it's now locked so are all other cells in that row

 

[bellman101]

It is already locked? It shouldn't be.

I'm wondering if you ran the code I posted yesterday:

Sheets("SheetName").Range("C14:D15").Locked = True
This was just some example code for you to modify.

IF you ran it though, it may have locked those cells.

I would try unlocking the whole sheet, and then starting again. See if you get the same behaviour.

Cheers
JB

 

[Cablek]

I added the Sheets command and now complete code looks like this

Private Sub Worksheet_change(ByVal Target As Excel.Range)
Sheets("Shipping").Range("B14:E100").Locked = True
If (Target.Count = 1) And (Not Intersect(Target, [B14:B10000]) Is Nothing) Then
ActiveSheet.Unprotect
Target.Offset(0, -1) = Date
Target.Offset(0, -1).Resize(1, 2).Locked = True
ActiveSheet.Protect
End If
Sheets("Shipping").Protect
End Sub

still getting an error but now it's
Run-time arror "1004"
Unable to set the locked property of the range class... is the range the problem?

REALLY REALLY SORRY JB I really appreciate your help in this

 

[bellman101]

IT'S REALLY REALLY FINE! don't worry!

Let's go through the logic again.

You want all cells UNLOCKED to start.

You enter something in Bx. This should trigger:

Ax = Today's date
Bx = "Text You Just Typed"
Ax becomes locked.
Bx becomes locked.

Cx:Ex are all unlocked.

So to start with you need all your editable cells unlocked, which can be achieved by running this once only:

Sub UnlockCells()

    Sheets("Shipping").Range("B14:E100").Locked = [U][B]False[/B][/U]    'This assumes "Shipping" is the sheet you are working with and B14:E100 is your working range?

End Sub

You don't want that running every time, or it will unlock all cells that were previously locked.

Now you want your WorksheetChange_Event:

[COLOR=#333333]Private Sub Worksheet_change(ByVal Target As Excel.Range)[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]If (Target.Count = 1) And (Not Intersect(Target, [B14:B10000]) Is Nothing) Then[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]    [/COLOR][COLOR=#333333]Sheets("Shipping").[/COLOR][COLOR=#333333]Unprotect[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]    [/COLOR][COLOR=#333333]Target.Offset(0, -1) = Date[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]    [/COLOR][COLOR=#333333]Target.Offset(0, -1).Resize(1, 2).Locked = True[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]    [/COLOR][COLOR=#333333]Sheets("Shipping").[/COLOR][COLOR=#333333]Protect[/COLOR]
[COLOR=#333333]    [/COLOR][COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

Just to explain the logic somewhat,

The 'Target' is the cell you have just edited, so B14 for example.
'Offset(0, -1)' defines the cell zero rows, and -1 columns from B14. 14 + 0 = 14. B + (-1) = A. Same as 2 + (-1) = 1.
Offset() is after Target, so it is (0, -1) away from 'Target'.
'Resize(1, 2)' adjusts the size of the range for that line of code. 1 means 1 row high (0 will produce an error as a selection cannot be zero rows), and the 2 means 2 columns wide.

So that line reads "Cell B14, -1 column = A14, resize to 1 row high, still = A14, resize to 2 columns wide = A14:B14. '.Locked' defines the property we are working with. = True sets the value of said property. So we're saying A14:B14 ARE locked.

Not sure if you know about stepping through code, but if not I think it will help you understand VBA much better, and very quickly. Normal macros in a module can be stepped through by putting the keyboard cursor within a Sub, and pressing F8. keep pressing F8 and it will step through 1 line at a time, exactly as it would when it runs on its own, but stopping every line for you to assess anything you need or want to.

Events cannot be stepped into. They can only be triggered by the even occurring, so you editing the worksheet in this case. However, if you place a breakpoint on line 1 (Private Sub...). You can do this by either clicking in the narrow column immediately to the left of that line of code, or place the cursor in the line and press F9. The line should turn dark red. Now go back and edit your sheet as you would normally, and the VBA window should appear immediately, with that line highlighted a mix of brown and yellow. NOW press F8, and you are stepping through your code. Changes happen live in the worksheet, so after a line, you can check it has done what it should. And you can also hover the mouse over any variables to check they have the expected value in them.

Think that should be enough for you to be on with! Off for a pint.

Cheers
JB