Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
Hi all i have this code below that stops the user using right click on cell how do i modify it so that they can not use right click on a shee tab? Regards, Simon Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVa 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 Su -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=56505 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
Hi Simon,
Try: '============= Public Sub Tester() Application.CommandBars("Ply").Enabled = False End Sub '<<============= --- Regards, Norman "Simon Lloyd" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
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" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing Right Click On Sheet Tab???
Norman thanks for the info and the help. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked Spreadsheets - Preventing Access to Source Sheet | Excel Discussion (Misc queries) | |||
shortcut for right click on sheet bar | Excel Discussion (Misc queries) | |||
Before right click in a sheet | Excel Programming | |||
Preventing users to move object in the sheet | Excel Programming | |||
Preventing someone from unhiding a sheet | Excel Programming |