Preventing Right Click On Sheet Tab???

[Simon Lloyd]

Hi all i have this code below that stops the user using right click on a cell how do i modify it so that they can not use right click on a sheet tab?

Regards,
Simon

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
ActiveWindow.DisplayHeadings = False
Cancel = True
ShortcutMenus(xlWorksheetCell).Enabled = False
MsgBox "You Can Only Use Your Left Mouse Button To Make Your Choice!", vbExclamation, "Mouse Warning"
End Sub

[NickHK]

Simon,
If you want to stop the user modify the Structure of the WB, protect the WB
instead of/as well as the WS.

NickHK

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all i have this code below that stops the user using right click on a
> cell how do i modify it so that they can not use right click on a sheet
> tab?
>
> Regards,
> Simon
>
> Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
> Target As Range, Cancel As Boolean)
> ActiveWindow.DisplayHeadings = False
> Cancel = True
> ShortcutMenus(xlWorksheetCell).Enabled = False
> MsgBox "You Can Only Use Your Left Mouse Button To Make Your Choice!",
> vbExclamation, "Mouse Warning"
> End Sub
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=565059
>

[Norman Jones]

Hi Simon,

Try:

'=============>>
Public Sub Tester()
Application.CommandBars("Ply").Enabled = False
End Sub
'<<=============


---
Regards,
Norman


"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all i have this code below that stops the user using right click on a
> cell how do i modify it so that they can not use right click on a sheet
> tab?
>
> Regards,
> Simon
>
> Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
> Target As Range, Cancel As Boolean)
> ActiveWindow.DisplayHeadings = False
> Cancel = True
> ShortcutMenus(xlWorksheetCell).Enabled = False
> MsgBox "You Can Only Use Your Left Mouse Button To Make Your Choice!",
> vbExclamation, "Mouse Warning"
> End Sub
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=565059
>

[Simon Lloyd]

Norman cheers! that worked........can you explain why it did? as i was a little wary about adding the line because in my Auto_open code i turn off command bars and turn on a custom bar, but didn't have to make any other reference to the one you supplied...............do i have to enable the bar when exiting the workbook?

Regards,
Simon

[Norman Jones]

Hi Simon.

The popup menu encountered in reponse to right-clicking a worksheet tab is
the Ply CommandBar. To orevent the popup, it is necessary to disable the
commandbar, as in my suggsted code.

The popup will remain disabled until it is explicitly re-enabled. Therefore,
if the popup is to be disabled only for a specific workbook, try disabling
the popup in the relevant workbook's open and deactivate events and
re-enable it in the Workbook_Deactivate event, e,g.:

'=============>>
Private Sub Workbook_Open()
Application.CommandBars("Ply").Enabled = False
End Sub

'--------------->

Private Sub Workbook_Deactivate()
Application.CommandBars("Ply").Enabled = True
End Sub

'--------------->

Private Sub Workbook_Activate()
Application.CommandBars("Ply").Enabled = False
End Sub
'<<=============

---
Regards,
Norman



"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Norman cheers! that worked........can you explain why it did? as i was a
> little wary about adding the line because in my Auto_open code i turn
> off command bars and turn on a custom bar, but didn't have to make any
> other reference to the one you supplied...............do i have to
> enable the bar when exiting the workbook?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=565059
>

[Norman Jones]

Hi Simon.

The popup menu encountered in reponse to right-clicking a worksheet tab is
the Ply CommandBar. To orevent the popup, it is necessary to disable the
commandbar, as in my suggsted code.

The popup will remain disabled until it is explicitly re-enabled. Therefore,
if the popup is to be disabled only for a specific workbook, try disabling
the popup in the relevant workbook's open and deactivate events and
re-enable it in the Workbook_Deactivate event, e,g.:

'=============>>
Private Sub Workbook_Open()
Application.CommandBars("Ply").Enabled = False
End Sub

'--------------->

Private Sub Workbook_Deactivate()
Application.CommandBars("Ply").Enabled = True
End Sub

'--------------->

Private Sub Workbook_Activate()
Application.CommandBars("Ply").Enabled = False
End Sub
'<<=============

---
Regards,
Norman



"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Norman cheers! that worked........can you explain why it did? as i was a
> little wary about adding the line because in my Auto_open code i turn
> off command bars and turn on a custom bar, but didn't have to make any
> other reference to the one you supplied...............do i have to
> enable the bar when exiting the workbook?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=565059
>

[Simon Lloyd]

Norman thanks for the info and the help.

Regards,
Simon

[Norman Jones]

Hi Simon,

As a potentially significant typo, note that:

> try disabling the popup in the relevant workbook's open and deactivate
> events

should read

try disabling the popup in the relevant workbook's open and activate events

as correctly reflected in the suggested code.

As an insignificant typo,

> To orevent the popup

should read

To prevent the popup

---
Regards,
Norman


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Simon.
>
> The popup menu encountered in reponse to right-clicking a worksheet tab is
> the Ply CommandBar. To orevent the popup, it is necessary to disable the
> commandbar, as in my suggsted code.
>
> The popup will remain disabled until it is explicitly re-enabled.
> Therefore, if the popup is to be disabled only for a specific workbook,
> try disabling the popup in the relevant workbook's open and deactivate
> events and re-enable it in the Workbook_Deactivate event, e,g.:
>
> '=============>>
> Private Sub Workbook_Open()
> Application.CommandBars("Ply").Enabled = False
> End Sub
>
> '--------------->
>
> Private Sub Workbook_Deactivate()
> Application.CommandBars("Ply").Enabled = True
> End Sub
>
> '--------------->
>
> Private Sub Workbook_Activate()
> Application.CommandBars("Ply").Enabled = False
> End Sub
> '<<=============
>
> ---
> Regards,
> Norman
>
>
>
> "Simon Lloyd" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> Norman cheers! that worked........can you explain why it did? as i was a
>> little wary about adding the line because in my Auto_open code i turn
>> off command bars and turn on a custom bar, but didn't have to make any
>> other reference to the one you supplied...............do i have to
>> enable the bar when exiting the workbook?
>>
>> Regards,
>> Simon
>>
>>
>> --
>> Simon Lloyd
>> ------------------------------------------------------------------------
>> Simon Lloyd's Profile:
>> http://www.excelforum.com/member.php...fo&userid=6708
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=565059
>>
>
>