Evening,
Glad it works. Adding in more actions for other columns is easy enough.
I don't want to just give you the answer though, as that won't help you learn VBA.
Lets start with your original code:
Code:
Private Sub Worksheet_change(ByVal Target As Excel.Range)
If (Target.Count = 1) And (Not Intersect(Target, [B14:B10000]) Is Nothing) Then
Sheets("Shipping").Unprotect
Target.Offset(0, -1) = Date
Target.Offset(0, -1).Resize(1, 2).Locked = True
Sheets("Shipping").Protect
End If
End Sub
So line 2 is the if statement. If that results in FALSE, then it will skip straight to 'End If', ignoring any code in between. It will then action any code after 'End If', which in this case is just 'End Sub'.
So if we add in some space beneath 'End If' we can write more code for the same Sub.
In your current If statement, you are saying Target.count must be 1. This is just saying that the user must have edited only one cell. (Copying multiple cells and pasting them into Column B will edit multiple cells at once, so will be ignored). This code is normally in place because commands on a single cell are often not designed for a range of cells, so will fail.
We are also saying 'NOT Intersect(Target, [B14:B10000]) is Nothing'. The Intersect checks that the 'Target' cell in in the range B14:B10000. You then have an 'Is Nothing', and the 'Not', which makes it all very confusing as it is a double negative.
You may find this easier to understand:
Code:
If Target.Column = 2 AND Target.Row >=14 And Target.Row <=10000 'This says exactly the same thing. (Target.column is numeric, but Column A=1, B=2, etc)
So now you want to add in another scenario for column G (I think?), so we need a whole new If statement, in the blank space you have created beneath the original one.
Column G is Column number 7.
So your next IF statement is:
Code:
If Target.Count = 1 AND Target.Column = 7 and Target.Row >=14 and Target.Row <=10000
'After this line hit Enter a few times and then put 'End If'.
This gives you a new If statement to work with, which is currently empty. so now we need to fill it.
Remember you cannot modify the sheet, (either manually or with code) if the sheet is protected, so the first line within the If statement needs to unprotect the sheet, which you now know how to do right?
There's no auto date or anything to enter for this scenario, so we just need to lock the cells, and re-protect the sheet.
A & B should be locked already, but it will do no harm to re-lock them, so we are going to lock Columns A - F. You now know that '.Locked = True' will lock the cells specified to the left of '.Locked', so let's specify those cells!
Target is always the cell you have edited. Let's say G14.
So to get to A we need to offset - Again, A = 1, G = 7, so to get back to A we need to offset by -6. Remember with offset the first number is always
Rows, the second is
Columns. We of course still want the same row, so zero offset there.
If you have written your Target.Offset correctly, then it will now be referring to cell A14.
So finally we need to resize that reference to produce A14:F14.
Resize, like offset has two parameters, and again like offset, they are in the order
Rows,Columns. We are still just working with the single row of data, so you still want your range to be 1 row high. However, you need it to be 6 columns wide. You can copy the 'Locked = True' line from the first if statement and adjust it accordingly if that makes things easier.
Hopefully now you have completed your line of code to lock the row.
Finally, before the 'End If' you will want to re-protect the sheet, which again, you should now know how to do.
You should now be ready to go.
If it does not behave as expected, then put a breakpoint on line 1 (Private Sub...) and then enter a person's name in G. You should then be in the code, which you can step through with F8, to try to diagnose the issue.
Good luck.
Make sure you let me know how you get on
Cheers
JB
