There is a way to allow vba access to locked sheets whilst keeping it locked for users, see the last example on the page linked below.
Excel VBA Macro. How To Have Your Excel Macros Run on Protected Worksheets
www.ozgrid.com
This works with local and network files, although I've not used it with any stored on one drive or similar locations. Alternatively you can allow the code to unlock the sheet while it runs, then lock again when it finishes. This would be done following the first example on the page that I have linked above. This would mean adding the unlock and lock lines to each individual procedure where as the workbook open method only needs that one piece of code to be added for all procedures.
Hi Jason75 and thanks for you input very well appreciated. pls how and where do I put the suggested lines In the code below .
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Dim r As Long
Application.ScreenUpdating = False
' First check range I3:J80 for updates
Set rng = Intersect(Target, Range("I3:J80"))
' If any cells found, loop through them and add to 3 columns to left
If Not rng Is Nothing Then
For Each cell In rng
' Get row being updated
r = cell.Row
' Update values
Application.EnableEvents = False
cell.Offset(0, -3) = cell.Offset(0, -3) + cell
' See if column G is greater than column column F
If Cells(r, "G") > Cells(r, "F") Then
' Reverse update above
cell.Offset(0, -3) = cell.Offset(0, -3) - cell
MsgBox "The stock will go negative - not allowed"
End If
' Clear original entry
cell = 0
Application.EnableEvents = True
Next cell
End If
Application.ScreenUpdating = True
End Sub
thanks