Hello,

Using VBA, I add items to the right-click menu with Workbook_Open(),
then remove these items on Workbook_BeforeClose(). The odd thing that
I haven't been able to figure out is why I can see the items from some
sheets in the workbook, but not other sheets. I would like the items
to be available to me, regardless of what sheet I'm on. Does anyone
know why this happens, and how I can fix it?

Thanks,
Carroll Rinehart

Hi Caroll

One possibility is that there is code in one, or more, of the sheet code
modules which edits the ply menu.

If this is not the case, post the relevant code from the Workbook_Open and
Workbook_Close event procedures.


---
Regards,
Norman



"Carroll" wrote in message
oups.com...
Hello,

Using VBA, I add items to the right-click menu with Workbook_Open(),
then remove these items on Workbook_BeforeClose(). The odd thing that
I haven't been able to figure out is why I can see the items from some
sheets in the workbook, but not other sheets. I would like the items
to be available to me, regardless of what sheet I'm on. Does anyone
know why this happens, and how I can fix it?

Thanks,
Carroll Rinehart

right_click menus differ depending on the kind of sheet and the kind of
object on the sheet.
e.g. a chart sheet or a pviot table have their own right click menus.
pls check where you do your right click.

DM Unseen

Hi DM Unseen,

right_click menus differ depending on the kind of sheet and the kind of
object on the sheet.

Indeed so and it is possible that the OP's experience is engenderd by moving
between worksheets and (say) chart sheets.

If, however, the OP experiences this phenomenon when moving between
worksheets, then I would look at the possibility of event code editing the
relevant right-click menu.

There may, of course be other possibilities and, fortunately, there are many
with greater insight than me.

---
Regards,
Norman



"DM Unseen" wrote in message
ups.com...
right_click menus differ depending on the kind of sheet and the kind of
object on the sheet.
e.g. a chart sheet or a pviot table have their own right click menus.
pls check where you do your right click.

DM Unseen

Hi,

Actually, I use this same technique. I have found that very subtle things
can cause a different shortcut menu to appear. For example, the presence of a
querytable in a sheet changes the shortcut menu that pops up. Also, if the
user is in a different view, ie. "PageBreak preview" vs. normal.

My work around is to loop through ALL shortcut menus and add my custom item
at the top. Even though I initially thought it may take too long to function
this way, it actually runs superbly and I use it routinely now.

If anyone would like to see it, post back and I can make it generic for
posting.

HTH.

"Norman Jones" wrote:

Hi DM Unseen,

right_click menus differ depending on the kind of sheet and the kind of
object on the sheet.

Indeed so and it is possible that the OP's experience is engenderd by moving
between worksheets and (say) chart sheets.

If, however, the OP experiences this phenomenon when moving between
worksheets, then I would look at the possibility of event code editing the
relevant right-click menu.

There may, of course be other possibilities and, fortunately, there are many
with greater insight than me.

---
Regards,
Norman



"DM Unseen" wrote in message
ups.com...
right_click menus differ depending on the kind of sheet and the kind of
object on the sheet.
e.g. a chart sheet or a pviot table have their own right click menus.
pls check where you do your right click.

DM Unseen

Here is the code:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
On Error GoTo 0

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "1.Import IDA Data"
.OnAction = ThisWorkbook.Name & "!Import"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "2.Add New Date Row"
.OnAction = ThisWorkbook.Name & "!AddNewRows"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "3.Save Reports"
.OnAction = ThisWorkbook.Name & "!SaveReports"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
End Sub

None of the sheets involve charts or pivot tables. They are all just
normal sheets.
I notice that the right-click menu changes when I'm in a named range.
I have since realized that it's not entire sheets that don't have the
items I added to the right-click menu; it's only when the active cell
happens to fall within a named range. Does that make sense? I'm
thinking now that there is a separate right-click menu for when you are
in named ranges?

Carroll

quartz,

Yes, a querytable is involved, so that explains it.
Yes, I would appreciate it if you you shared your code.

Thanks,

Carroll

Hi Carroll,

As pointed out by Quartz in an adjacent post, the right-click menu is
subject to subtle change. You have provided an apparent instance os such
change, although it is not one that I have noticed.

John Green provides a nice CBList addin which, amongst other things, lists
all popup menus and their constituent elements. It is freely downloadable
from Rob Bovey's Office automation site:

http://www.oaltd.co.uk/MVP/Default.htm

You could loop through the relevant menu bars as suggested by Quartz and he
has offered working code to do this.


---
Regards,
Norman



"Carroll" wrote in message
oups.com...
Here is the code:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
On Error GoTo 0

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "1.Import IDA Data"
.OnAction = ThisWorkbook.Name & "!Import"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "2.Add New Date Row"
.OnAction = ThisWorkbook.Name & "!AddNewRows"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "3.Save Reports"
.OnAction = ThisWorkbook.Name & "!SaveReports"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
End Sub

None of the sheets involve charts or pivot tables. They are all just
normal sheets.
I notice that the right-click menu changes when I'm in a named range.
I have since realized that it's not entire sheets that don't have the
items I added to the right-click menu; it's only when the active cell
happens to fall within a named range. Does that make sense? I'm
thinking now that there is a separate right-click menu for when you are
in named ranges?

Carroll

Here you go. I use the "activate" event so that if a user switches to another
workbook, the custom item goes away. I put the following two subs in
"ThisWorkbook" module which call the actual programs:

Private Sub Workbook_Activate()
Call ShortCutMenuModify
End Sub

Private Sub Workbook_Deactivate()
Call ShortCutMenuReset
End Sub

The following two functions I place in a standard code module (please note
you will have to adjust for line wrapping), also see my notes where you will
need to change my info for yours:

Public Function ShortCutMenuModify()
'ADD CUSTOM CONTROL TO TOP OF EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU:
CALLED ON WORKBOOK ACTIVATE
Dim cmdBar As CommandBar
Dim lngX As Long
For lngX = 1 To Application.CommandBars.Count
If CommandBars(lngX).Type = msoBarTypePopup _
And CommandBars(lngX).BuiltIn = True _
And lngX < 28 Then 'TYPE=2
Set cmdBar = Application.CommandBars(lngX)
With cmdBar
.Controls.Add Type:=msoControlButton, Befo=1
.Controls(1).Caption = "Custom" '< change this to your menu
item name
.Controls(1).FaceId = 5828 '< change or remove custom icon
.Controls(1).OnAction = "MyMacro" '< your program name to run
End With
End If
Next lngX
End Function


Public Function ShortCutMenuReset()
'RESET EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU: CALLED ON WORKBOOK
DEACTIVATE
Dim cmdBar As CommandBar
Dim lngX As Long
For lngX = 1 To Application.CommandBars.Count
If CommandBars(lngX).Type = msoBarTypePopup And
CommandBars(lngX).BuiltIn = True Then CommandBars(lngX).Reset
Next lngX
End Function

I hope this helps. Post back if you have problems.

"Carroll" wrote:

Here is the code:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
On Error GoTo 0

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "1.Import IDA Data"
.OnAction = ThisWorkbook.Name & "!Import"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "2.Add New Date Row"
.OnAction = ThisWorkbook.Name & "!AddNewRows"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "3.Save Reports"
.OnAction = ThisWorkbook.Name & "!SaveReports"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
End Sub

None of the sheets involve charts or pivot tables. They are all just
normal sheets.
I notice that the right-click menu changes when I'm in a named range.
I have since realized that it's not entire sheets that don't have the
items I added to the right-click menu; it's only when the active cell
happens to fall within a named range. Does that make sense? I'm
thinking now that there is a separate right-click menu for when you are
in named ranges?

Carroll

I haven't experienced the issue with named ranges. When I discovered the
various changes in the menu based on the user context, I just modified the
code to alter EVERY right click menu. This works very well, even if the user
right clicks on a row number or column letter (to select the entire row or
column) the custom menu is available.

Hopefully this will work for you...

"Carroll" wrote:

Here is the code:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
On Error GoTo 0

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "1.Import IDA Data"
.OnAction = ThisWorkbook.Name & "!Import"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "2.Add New Date Row"
.OnAction = ThisWorkbook.Name & "!AddNewRows"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "3.Save Reports"
.OnAction = ThisWorkbook.Name & "!SaveReports"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
End Sub

None of the sheets involve charts or pivot tables. They are all just
normal sheets.
I notice that the right-click menu changes when I'm in a named range.
I have since realized that it's not entire sheets that don't have the
items I added to the right-click menu; it's only when the active cell
happens to fall within a named range. Does that make sense? I'm
thinking now that there is a separate right-click menu for when you are
in named ranges?

Carroll

Thanks for that info Norman. I will check out John Green's addin too. ;-)

"Norman Jones" wrote:

Hi Carroll,

As pointed out by Quartz in an adjacent post, the right-click menu is
subject to subtle change. You have provided an apparent instance os such
change, although it is not one that I have noticed.

John Green provides a nice CBList addin which, amongst other things, lists
all popup menus and their constituent elements. It is freely downloadable
from Rob Bovey's Office automation site:

http://www.oaltd.co.uk/MVP/Default.htm

You could loop through the relevant menu bars as suggested by Quartz and he
has offered working code to do this.


---
Regards,
Norman



"Carroll" wrote in message
oups.com...
Here is the code:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
On Error GoTo 0

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "1.Import IDA Data"
.OnAction = ThisWorkbook.Name & "!Import"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "2.Add New Date Row"
.OnAction = ThisWorkbook.Name & "!AddNewRows"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "3.Save Reports"
.OnAction = ThisWorkbook.Name & "!SaveReports"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("1.Import IDA Data").Delete
Application.CommandBars("Cell").Controls("2.Add New Date Row").Delete
Application.CommandBars("Cell").Controls("3.Save Reports").Delete
End Sub

None of the sheets involve charts or pivot tables. They are all just
normal sheets.
I notice that the right-click menu changes when I'm in a named range.
I have since realized that it's not entire sheets that don't have the
items I added to the right-click menu; it's only when the active cell
happens to fall within a named range. Does that make sense? I'm
thinking now that there is a separate right-click menu for when you are
in named ranges?

Carroll

Thanks everyone for your comments, and thanks quartz for your code.
Now that I think about it, the right-click menu was related to the
querytable and not the named range, even though both were involved.
Also quartz, your comment about being in page-break view also proved to
be true. Without realizing it, one of my sheets was in page-break view
and the right-click menu applied to that situation. When I put it in
normal view, I did see my added menu items.

Carroll

Quartz, I tried your code and it works great, except I have other
CommandBar items I do not want to reset. How do I modify your code to
only affect the item that was added in WB activation? TIA

Greg