Error running vba code one drive shared excel sheet

[AOGFADUGBA]

Hi all. I shared an excel file on one drive. some of the cells are locked. The problem here is that excel prevent the vba code from run unless the locked cells are unlocked. the cells are locked to restrict access. so unlocking it will defeat the whole propose. can anyone help with this. attached to this post are the screenshot of the error and the VBA code

 

[jasonb75]

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 Code To Run Macros On Protected Worksheets & Sheets

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.

 

[AOGFADUGBA]

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 Code To Run Macros On Protected Worksheets & Sheets

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

 

[jasonb75]

If you want to simply unprotect the sheet for that code then it would be like this, you will need to use your correct password in place of "Secret". This is in two places in your code, one at the top and another at the bottom.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:="Secret"

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
Me.Protect Password:="Secret"
End Sub

If you want to use the other method that allows all code access to the sheet but still protects it from the user then you need to add the code from the linked page to the workbook module by following the instructions there.

 

[AOGFADUGBA]

If you want to simply unprotect the sheet for that code then it would be like this, you will need to use your correct password in place of "Secret". This is in two places in your code, one at the top and another at the bottom.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:="Secret"

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
Me.Protect Password:="Secret"
End Sub

If you want to use the other method that allows all code access to the sheet but still protects it from the user then you need to add the code from the linked page to the workbook module by following the instructions there.

Thanks a million JasonB75, the modification worked perfectly well. The only problem is that there is a conditional formatting in column E3: down, for the color of the cell to change if a condition is met. i.e. the color of cell E3 should change from green to red if its value is less than 6.
but the conditional formatting has stopped working. see attached screenshot.

please can you help with this. many thanks

 

[jasonb75]

The conditional formatting problem would not be in any way related to the vba code that you have applied. There is nothing in there that would change the conditional formatting or prevent it from working.

From the screen capture I would guess that you have set up the rule to change red when E3<=D3 and not when E3<6 as you say.

 

[AOGFADUGBA]

The conditional formatting problem would not be in any way related to the vba code that you have applied. There is nothing in there that would change the conditional formatting or prevent it from working.

From the screen capture I would guess that you have set up the rule to change red when E3<=D3 and not when E3<6 as you say.

Thanks a lot, you are right. I figured it out.
But I didn't get the other option you referred to in your post before this.
Can you please clarify that option. Thanks JasonB75

 

[jasonb75]

Do you have more than one macro that you need to unprotect the sheet for, or is it just this one each time it runs?

If it is only for this one then you do not need the alternative.

For more than one, you can use the alternative method detailed in the page that I linked earlier. It starts from the heading, UserInterFaceOnly which is about halfway down the page but it would be beneficial to read everything. All of the information you need to set it up is on that page.