Rigth Click menu

[PerJ]

Hi
I have a problem with adding commands, using VBA, to the commandbar that
shows when you right click a cell in Excel.

The code I use:

Private Sub Workbook_Open()
Dim btn As CommandBarControl

Application.CommandBars("Cell").Reset

Set btn = Application.CommandBars("Cell").Controls.Add(msoControlButton)
With btn
.Style = msoButtonCaption
.Caption = "Test"
.OnAction = "action1"
End With
End If
Next i
End Sub

I'm doing this in Excel 2003

After running the code I can se that the Cell commanbar contains the new
item, but when I rigth click a cell, it doesn't show in the menu.

Can anyone help?

 

[Bob Phillips]

There is a superfluous End If and Next i in that code that stops it from
running.

Are you putting the code in the ThisWorbook code module? It should only run
when you open the workbook.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[PerJ]

Hi Bob

Yes I saw to late that I had pasted some code that wouldn't run. Sorry about
that.

I've tried to put it in workbook_open and in Workbook_SheetBeforeRightClick,
but nothing works.

 

[Dave Peterson]

It's time to share what you really put in the workbook_open event code that's in
the ThisWorkbook module.

 

[PerJ]

Hi Dave and others

Here is the code I'm trying to get to work.

Private Sub Workbook_Open()
Dim btn As CommandBarControl

For i = 1 To Application.CommandBars.Count
If Application.CommandBars(i).Name = "Cell" Then
Set btn = Application.CommandBars(i).Controls.Add(msoControlButton)
With btn
.Style = msoButtonCaption
.Caption = "Test"
.OnAction = "action1"
End With
End If
Next i

End Sub

I did the iteration through the commandbars, because I discovered that there
are 2 commandbars with the name: "Cell" and I therefore thought that I was
updateding the wrong one.

 

[Dave Peterson]

Your code worked ok for me.

But I would declare i:
Dim i as long

Are you sure you put the code under the ThisWorkbook module?
Do your security settings allow macros to run?
How are you opening this workbook?
Did you save, close, and reopen the workbook--so the workbook_open event would
have a chance to fire?

There are two commandbars named cell--one is when you're in normal view and the
other when you're in page break preview mode. I don't know why MS assigned the
same name to each. Seems like a bad idea to me.

And you may want to clean up before you start and after you're done:

Option Explicit
Private Sub Workbook_Open()
Dim btn As CommandBarControl
Dim i As Long

For i = 1 To Application.CommandBars.Count
If Application.CommandBars(i).Name = "Cell" Then
On Error Resume Next
Application.CommandBars(i).Controls("test").Delete
On Error GoTo 0
Set btn = Application.CommandBars(i).Controls.Add(msoControlButton)
With btn
.Style = msoButtonCaption
.Caption = "Test"
.OnAction = "action1"
End With
End If
Next i

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim btn As CommandBarControl
Dim i As Long

For i = 1 To Application.CommandBars.Count
If Application.CommandBars(i).Name = "Cell" Then
On Error Resume Next
Application.CommandBars(i).Controls("test").Delete
On Error GoTo 0
End If
Next i

End Sub

 

[Bob Phillips]

Use

Private Sub Workbook_Open()
Dim btn As CommandBarControl

With Application.CommandBars("Cell")
Set btn = .Controls.Add(msoControlButton)
With btn
.Style = msoButtonCaption
.Caption = "Test"
.OnAction = "action1"
End With
End With
End Sub

and make sure you put it in ThisWorkbook.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[PerJ]

Hi Dave

First of all thank you for your time.

I've tried your code and it still wont work for me.
To answer your questions:
-I have saved, close and reopened the workbook several times. -I am allowed
to run macroes. -I start Excel by doubleclicking the workbook.

I've tried to shift the mode to page break preview and in this mode it works
fine. Strange!

Best regards
Per

 

[PerJ]

Hi Bob

Thank you for the example, but i do not work on my machine :-(

br / Per

 

[Dave Peterson]

What does "won't work for me" mean?

Did you put the "action1" macro in a general module?

Hi Dave

First of all thank you for your time.

I've tried your code and it still wont work for me.
To answer your questions:
-I have saved, close and reopened the workbook several times. -I am allowed
to run macroes. -I start Excel by doubleclicking the workbook.

I've tried to shift the mode to page break preview and in this mode it works
fine. Strange!

Best regards
Per

 

[PerJ]

Hi Dave

Sorry for not being specific in my answer.
What I meant was that I still don't get the "Test" item in the right click
menu.

Best regards

Per

 

[PerJ]

Hi Dave

Found the reason why the I couldn't get the rigth click menu to work as I
wanted.
I uninstalled all the addins to excel and now everything works OK.
I started to install the addins, one by one and test every time and found
the addin that coursed the problem.

Once again thank you for your time.

Best regards

Per