Before right click:
 

I have this as the first lines of code under an open workbook in it's:

This Workbook
Apps
Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)

---code---
If ActiveSheet.ProtectContents = True And ActiveCell.Locked = True Then
With Application.CommandBars("Cell").Controls
.Item(2).Enabled = False
End With
Else
With Application.CommandBars("Cell").Controls
.Item(2).Enabled = True
End With
End If


It always will return True no matter if the workbook is protected or not.
But if I use the code above on a right click menu custom item after the item
is clicked then it correctly identifies if the sheet is protected or not.

What am I doing wrong here?

Thank you for your help.

Steven

Before right click:
 

I'm not sure what is returning True (.item(2).enabled???).

But this worked ok for me:

Option Explicit
Public WithEvents App As Excel.Application
Private Sub Workbook_Open()
Set App = Application
End Sub
Private Sub Workbook_Close()
Set App = Nothing
End Sub
Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)

If Sh.ProtectContents = True _
And Target.Cells(1).Locked = True Then
With Application.CommandBars("Cell").Controls
.Item(2).Enabled = False
End With
Else
With Application.CommandBars("Cell").Controls
.Item(2).Enabled = True
End With
End If
End Sub

(I had all the code in the ThisWorkbook module.)

Since excel passes the sheet (as Sh) and the range (as Target), I'd use them.

Steven wrote:

I have this as the first lines of code under an open workbook in it's:

This Workbook
Apps
Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)

---code---
If ActiveSheet.ProtectContents = True And ActiveCell.Locked = True Then
With Application.CommandBars("Cell").Controls
.Item(2).Enabled = False
End With
Else
With Application.CommandBars("Cell").Controls
.Item(2).Enabled = True
End With
End If

It always will return True no matter if the workbook is protected or not.
But if I use the code above on a right click menu custom item after the item
is clicked then it correctly identifies if the sheet is protected or not.

What am I doing wrong here?

Thank you for your help.

Steven

--

Dave Peterson