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 B
x. This should trigger:
A
x = Today's date
B
x = "
Text You Just Typed"
A
x becomes locked.
B
x becomes locked.
C
x:E
x are all unlocked.
So to start with you need all your editable cells unlocked, which can be achieved by running this once only:
Code:
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:
Code:
[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