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

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

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

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

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

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

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

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" <(E-Mail Removed)> wrote in message
news:689FBF8F-24A2-4B48-A3FF-(E-Mail Removed)...
|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

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" <(E-Mail Removed)> wrote in message
> news:689FBF8F-24A2-4B48-A3FF-(E-Mail Removed)...
> |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
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:689FBF8F-24A2-4B48-A3FF-(E-Mail Removed)...
> > |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
> >
> >
> >