Add to Shortcut Menu

[asawyer]

The following code adds an item to the cells shortcut menu.

I do want it disabled at this point.

Option Explicit

Private Sub Workbook_Open()

Dim NewItem As CommandBarButton
'Stop

Application.CommandBars("Cell").Reset

Set NewItem = Application.CommandBars("Cell").Controls.Add
With NewItem
.Caption = "Show Detail"
.OnAction = "ShowDetail"
.BeginGroup = True
End With

Application.CommandBars("Cell").Controls("Show Detail").Enabled = False

End Sub


This works on my Excel 2003 system, and quite a few Excel 2000 systems, but
there is one that it just won't work on.
If I step thru the code, I get no errors, but it doesn't add it.

I have macro security set to low.

Is there something I'm missing, like something that doesn't allow my VBA
code to change shortcuts??


Thanks,
Alan Sawyer

 

[asawyer]

If I put a msgbox in the code, it displays it fine, so I know it's happy
executing VBA code.
Alan

 

[asawyer]

I also exited out of Excel, and made sure excel wasn't still in memory, just
in case the security setting had been recently changed.
I have another computer with the same exact version of Excel and it works.
Alan

 

[asawyer]

On the computer that doesn't work, I noticed it didn't have the MS Forms 2.0
Object Library in it's references, so I added that, but it didn't change
anything.
Alan

 

[Jim Cone]

Alan,

Your code works for me on XL 2002.
It adds the new item to the menu and disables it.
I did not test in a Workbook open event however.
What happens if you run it this way...

With NewItem
.Caption = "Show Detail"
.OnAction = "ShowDetail"
.BeginGroup = True
.Enabled = False '<<<new line
End With

'Remove following line...
Application.CommandBars("Cell").Controls("Show Detail").Enabled = False

Regards,
Jim Cone
San Francisco, USA


The following code adds an item to the cells shortcut menu.
I do want it disabled at this point.
Option Explicit
Private Sub Workbook_Open()
Dim NewItem As CommandBarButton
'Stop
Application.CommandBars("Cell").Reset
Set NewItem = Application.CommandBars("Cell").Controls.Add
With NewItem
.Caption = "Show Detail"
.OnAction = "ShowDetail"
.BeginGroup = True
End With
Application.CommandBars("Cell").Controls("Show Detail").Enabled = False
End Sub


This works on my Excel 2003 system, and quite a few Excel 2000 systems, but
there is one that it just won't work on.
If I step thru the code, I get no errors, but it doesn't add it.
I have macro security set to low.
Is there something I'm missing, like something that doesn't allow my VBA
code to change shortcuts??
Thanks,
Alan Sawyer

 

[Dave Peterson]

Your code worked as-is for me (xl2003).

But I've always tried to be more explicit with the .add statement. I also added
a .visible statement (just in case???)

Option Explicit

Private Sub Workbook_Open()

Dim NewItem As CommandBarButton
'Stop
On Error Resume Next
Application.CommandBars("Cell").Controls("Show Detail").Delete
On Error GoTo 0

Set NewItem = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Show Detail"
.OnAction = "ShowDetail"
.Visible = True
.BeginGroup = True
End With

Application.CommandBars("Cell").Controls("Show Detail").Enabled = False

End Sub


I also didn't like resetting the toolbar. The user may lose his/her
customizations, too.