Glenin,
In the code below I have taken an entirely different approach at the problem, so please let me know if it suits your needs.
Rather than placing this code in the 'Change' event, it is in the 'SelectionChange' event, which is triggered when a new cell is selected.
Rather than getting bogged down in locking & unlocking cells, and protecting / unprotecting the worksheet, the code instead checks when a cell is selected.
When the cell is selected the code will check the other values on that row to see if the cell is valid to have an entry put into it. If it is not it will warn the user, and move the cursor back out of the cell.
There are potentially ways around this protection, but it should stop your casual user from typing in a cell by mistake.
Let me know what you think please, and of course feel free to question anything you are unsure of.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim new_row As Integer
new_row = Target.Row ' Capture which row the cursor has just been placed on.
'Now do your tests to find out if the user is allowed in this cell
' Receipts that do not include a GST component
' Check for the values, (column I is column 9, column J is column 10, new_row is the row that the cursor is now on)
If Cells(new_row, 9).Value + Cells(new_row, 10).Value = 0 And _
Cells(new_row, 9).Value <> "" And Cells(new_row, 10).Value <> "" Then
' Not allowed entries in columns K,L,M,N (11,12,13,14)
Select Case Target.Column
Case 11, 12, 13, 14:
' Warn the user
MsgBox "You cannot use this cell as the reciept does not include GST"
' Move the cursor back out of this cell
Cells(new_row, 9).Activate
End Select
End If
' Receipts that do include a GST component
' Check for the values, (column E is 5, I is 9, J is 10, M is 13, new_row is the row that the cursor is now on)
If Cells(new_row, 5).Value <> "" And _
Cells(new_row, 5).Value = Cells(new_row, 9).Value + Cells(new_row, 10).Value + Cells(new_row, 13).Value Then
' Not allowed entries in columns O,P,Q,R (15,16,17,18)
Select Case Target.Column
Case 15, 16, 17, 18:
' Warn the user
MsgBox "You cannot use this cell as there is not a GST exclusive amount"
' Move the cursor back out of this cell
Cells(new_row, 9).Activate
End Select
End If
End Sub
Bookmarks