CODEWITHSUNDEEP
excelvbasharepointlocking
isaadiya
isaadiya

Reputation: 1

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

Is there a way to lock cells after a user inputs data into an excel sheet on sharepoint. I am able to lock the cells on excel but not sharepoint. Basically, it doesn't lock cells on sharepoint the same way it does when you input data on the excel app.

Is there a work around this? I've already tried VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A1:G800"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="saag"
    xRg.Locked = True
    Target.Worksheet.Protect Password:="saag"
End Sub

It doesn't work the same way as you'd expect when someone inputs data on share point.

enter image description here

Upvotes: 0

Views: 587

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

VBA will not work in the Excel Web version (which it appears you're using based on screen shot). Depending on the security settings, you might be able to get it to work if the user opens the file in their local Excel Application, but that seems destined to be problematic.

As an alternative, you could setup a PowerApp script that would function relatively similarly to the OnEdit event. You would need to use TypeScript instead of VBA. This video is a pretty good starter video for making a transition. You can turn on the TypeScript Recorder and capture a few actions, and then use the microsoft documentation to see interaction with various objects like range and their properties (value, locked, etc).

Upvotes: 1

Related Questions