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

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

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

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

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


wrote in message

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

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.

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