Prev: VBA to export large tables from Excel to SQL Server
Next: lock first row and first column WITHOUT selecting Cell "B2" first?
From: Steven on 26 Apr 2010 13:34
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

From: Dave Peterson on 26 Apr 2010 15:06
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
 | 
Pages: 1
Prev: VBA to export large tables from Excel to SQL Server
Next: lock first row and first column WITHOUT selecting Cell "B2" first?