Add to Shortcut Menu

[[email protected]]

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

[[email protected]]

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

[[email protected]]

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

[[email protected]]

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


<[email protected]> wrote in message
news:[email protected]
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.

[email protected] wrote:
>
> 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