Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Hi,
All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
it works for me (i.e., right click menu does not pop up)... so I assume by
"not working" you mean you don't see the message that says right click is disabled.. Add this as the last line of code before the End Sub line MsgBox msg -- Hope that helps. Vergel Adriano "Karen53" wrote: Hi, All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
You are never displaying your message box for starters. You need to
add: Msgbox msg Also, make sure that you have this code in the module for the sheet that you are trying to disable the right click for. Karen53 wrote: Hi, All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Forgot to post the finalized code. Edited a bit and untested:
Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = "You are not authorized to delete this sheet" & _ Chr(10) & Chr(10) & "Make all changes on Main Page" MsgBox msg End Sub Karen53 wrote: Hi, All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Hi,
I've added the line msgbox msg, confimed my code is on the correct sheet but it still allows me to right click. I am able to right click and rename the sheet. Is there anything alse I need to do? Thanks, "Karen53" wrote: Hi, All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Ok, this is weird. I've discovered I am locked out of right clicking on the
individual cells of the worksheet, not the sheet tab. How do I fix this? "Karen53" wrote: Hi, All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
I take it I can't turn off the right click on the sheet tab?
"Karen53" wrote: Hi, All of my research says this is the correct formula. However it is not working. Am I missing something? Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) 'Turn off right mouse click and display message Dim msg As String Cancel = True msg = ("You are not authorized to delete this sheet") & vbCtlf msg = msg & vbCtlf msg = msg & ("Make all changes on Main Page") End Sub Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Excel exposes a limited number of events that we can write code handlers
for. The cell right before click event is one of them but there is no event for many other events including right-clicking a worksheet tab. You can disable this pop up menu however if you run this code when the workbook opens (in WorkBook_Open event or sub Auto_Open): CommandBars("Ply").Enabled = False But! That code makes a permanent change to the users' toolbars which they will not be happy about you doing. So be sure to change it back when your workbook closes (Workbook_BeforeClose or sub Auto_Close). CommandBars("Ply").Enabled = True -- Jim "Karen53" wrote in message ... |I take it I can't turn off the right click on the sheet tab? | | "Karen53" wrote: | | Hi, | | All of my research says this is the correct formula. However it is not | working. Am I missing something? | | Private Sub Worksheet_BeforeRightClick _ | (ByVal Target As Range, Cancel As Boolean) | 'Turn off right mouse click and display message | Dim msg As String | | Cancel = True | msg = ("You are not authorized to delete this sheet") & vbCtlf | msg = msg & vbCtlf | msg = msg & ("Make all changes on Main Page") | | End Sub | | | Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Thanks Jim,
I've discovered protecting the workbook structure will do the same thing, and it's safer. Thanks for the info. "Jim Rech" wrote: Excel exposes a limited number of events that we can write code handlers for. The cell right before click event is one of them but there is no event for many other events including right-clicking a worksheet tab. You can disable this pop up menu however if you run this code when the workbook opens (in WorkBook_Open event or sub Auto_Open): CommandBars("Ply").Enabled = False But! That code makes a permanent change to the users' toolbars which they will not be happy about you doing. So be sure to change it back when your workbook closes (Workbook_BeforeClose or sub Auto_Close). CommandBars("Ply").Enabled = True -- Jim "Karen53" wrote in message ... |I take it I can't turn off the right click on the sheet tab? | | "Karen53" wrote: | | Hi, | | All of my research says this is the correct formula. However it is not | working. Am I missing something? | | Private Sub Worksheet_BeforeRightClick _ | (ByVal Target As Range, Cancel As Boolean) | 'Turn off right mouse click and display message | Dim msg As String | | Cancel = True | msg = ("You are not authorized to delete this sheet") & vbCtlf | msg = msg & vbCtlf | msg = msg & ("Make all changes on Main Page") | | End Sub | | | Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Right Click
Ah, I had no idea you were talking about right-clicking on the sheet
tab. I assumed you were just talking about right-clicking in the dccument. Glad you found a simple resolution. Karen53 wrote: Thanks Jim, I've discovered protecting the workbook structure will do the same thing, and it's safer. Thanks for the info. "Jim Rech" wrote: Excel exposes a limited number of events that we can write code handlers for. The cell right before click event is one of them but there is no event for many other events including right-clicking a worksheet tab. You can disable this pop up menu however if you run this code when the workbook opens (in WorkBook_Open event or sub Auto_Open): CommandBars("Ply").Enabled = False But! That code makes a permanent change to the users' toolbars which they will not be happy about you doing. So be sure to change it back when your workbook closes (Workbook_BeforeClose or sub Auto_Close). CommandBars("Ply").Enabled = True -- Jim "Karen53" wrote in message ... |I take it I can't turn off the right click on the sheet tab? | | "Karen53" wrote: | | Hi, | | All of my research says this is the correct formula. However it is not | working. Am I missing something? | | Private Sub Worksheet_BeforeRightClick _ | (ByVal Target As Range, Cancel As Boolean) | 'Turn off right mouse click and display message | Dim msg As String | | Cancel = True | msg = ("You are not authorized to delete this sheet") & vbCtlf | msg = msg & vbCtlf | msg = msg & ("Make all changes on Main Page") | | End Sub | | | Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable right click menu | Excel Programming | |||
Disable Right click | Excel Programming | |||
Disable right-click | Excel Programming | |||
Disable right click | Excel Programming | |||
Disable Window Right Click | Excel Programming |