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
Working with Shortcut Menus
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Working with Shortcut Menus
You can use the Workbook_SheetActivate event procedure in the ThisWorkbook module:
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_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Application.CommandBars("Cell").Controls("&Listings...").Visible = (Sh.Name <> "Sheet1")
End Sub
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
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Working with Shortcut Menus
Thanks Hans,
For your precise solutions.
Also, Your advice on a suitable book has paid off.
Thanks again
Mohamed
For your precise solutions.
Also, Your advice on a suitable book has paid off.
Thanks again
Mohamed
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Working with Shortcut Menus
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Working with Shortcut Menus
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)?
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
Hans