Working with Shortcut Menus

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Working with Shortcut Menus

Post by MSingh »

Hi,

I customised the pop-up menu on right-click using "Working with Shortcut Menus" by J Walkenbach.

How can i disable/ set visible=False a specific item on my customised popup. This would only apply to Sheet1.

Using the extract below which shows frmListsings how can i prevent frmListings from showing in sheet1 ?

Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
.Caption = "&Listings..."
.OnAction = "ShowListings"
.FaceId = 53

Thanks again
Mohamed

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Working with Shortcut Menus

Post by HansV »

You can use the Workbook_SheetActivate event procedure in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  On Error Resume Next
  Application.CommandBars("Cell").Controls("&Listings...").Visible = (Sh.Name <> "Sheet1")
End Sub
You may also want to hide the item when thec user switches to another workbook and when the user closes the workbook.

Code: Select all

Private Sub Workbook_Activate()
  On Error Resume Next
  Application.CommandBars("Cell").Controls("&Listings...").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Application.CommandBars("Cell").Controls("&Listings...").Visible = False
End Sub

Private Sub Workbook_Deactivate()
  On Error Resume Next
  Application.CommandBars("Cell").Controls("&Listings...").Visible = False
End Sub
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Working with Shortcut Menus

Post by MSingh »

Thanks Hans,

For your precise solutions.

Also, Your advice on a suitable book has paid off.

Thanks again
Mohamed

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Working with Shortcut Menus

Post by MSingh »

Hi Hans,

I seem to have posted a response too soon...

The code for disabling of the shortcut wouldn't work, perhaps its just unique to the parameters of my workbook?
(Indeed that wouldn't be the first time that a code didn't work becoz of the unique setup of a worksheet.)

But if you have an alternative solution, I would appreciate another attempt.

& thanks for all the solutions that did work!

Kind Regards
Mohamed

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Working with Shortcut Menus

Post by HansV »

You must use the same command bar in which you created the control (menu item). I assumed that you used the "Cell" command bar because that's the default right-click menu for a cell, but if you used a different one, you must use that in the code.

And make sure that you use the name of the control exactly as you created it.

If that doesn't help, could you either post the code you're using to create the control and to show/hide/enable/disable it, or attach a stripped down copy of your workbook (without sensitive data)?
Best wishes,
Hans