right click menu

[Guest]

In my workbook, the user right clicks on the "List" sheet, a form is
displayed, and some code is run. I disable the right click menu with the
following code:

Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name = "List" Then
Application.CommandBars("Cell").Enabled = False
Else
Application.CommandBars("Cell").Enabled = True
End If

Once the code is finished, I activate the "Results" sheet. On this sheet, I
need the the right click feature to be available.

Currently, when the code stops, the user sees the "Results" sheet, however
the right click menu is displayed. It's annoying because the user right
clicked on the 'List" sheet but not on the "Results" sheet. Is there a way
to close the right click menu programatically?

Thanks for the help....





End Sub

 

[Dave Peterson]

I'm not sure I understand for sure, but maybe you could stop using that event
and just use the worksheet_beforerightclick under the List worksheet:

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
UserForm1.Show
End Sub


Or maybe..

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
if intersect(target,me.range("a1:b9")) is nothing then
'do nothing
else
Cancel = True
UserForm1.Show
end if
End Sub

That way only rightclicking in A1:B9 would pop up that userform--everywhere else
gets the popup.

 

[Leith Ross]

In my workbook, the user right clicks on the "List" sheet, a form is
displayed, and some code is run. I disable the right click menu with the
following code:

Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name = "List" Then
Application.CommandBars("Cell").Enabled = False
Else
Application.CommandBars("Cell").Enabled = True
End If

Once the code is finished, I activate the "Results" sheet. On this sheet, I
need the the right click feature to be available.

Currently, when the code stops, the user sees the "Results" sheet, however
the right click menu is displayed. It's annoying because the user right
clicked on the 'List" sheet but not on the "Results" sheet. Is there a way
to close the right click menu programatically?

Thanks for the help....

End Sub

Helo JT,

You code is running after the event has taken place. Move your code to
the Worksheet_BeforeRightClick() event on the "List" worksheet. Then
your code will excute before the default Right_Click is processed by
Excel.

Sincerely,
Leith Ross

 

[Guest]

Thanks to both of you...that did the trick.......Thanks again