Macro - onAction arguments

[version83]

Hello.
I have this code:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVa
Target As Range, Cancel As Boolean)
Application.CommandBars("Cell").Reset
For Each Worksheet In Application.Worksheets
With Application.CommandBars("Cell").Controls
With .Add
.Caption = Worksheet.Name
.OnAction = "someMacro"
.Tag = "someTag"
.BeginGroup = True
End With
End With
Next
End Sub

It adds all sheets in the context menu.
There is just one more thing to be done.
When you click on a sheet from the context menu, it must becam
active.
Something like .OnAction = Worksheet.Select or a separate macro ....
I am not sure how to do it so i need some advice

 

[Dave Peterson]

Excel keeps track of what control you click on and you can use that:

Option Explicit
Sub SomeMacro()
MsgBox Application.CommandBars.ActionControl.Caption
thisworkbook.worksheets(Application.CommandBars.ActionControl.Caption) _
.select
End Sub

But...
It's not a good idea to use a variable that shares a name with a VBA keyword
(like Worksheet).

Option Explicit
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)

Dim wks As Worksheet

'this is usually a bad idea!
'You just reset the user's rightclick menu popup!
Application.CommandBars("Cell").Reset

For Each wks In Me.Worksheets
With Application.CommandBars("Cell").Controls
With .Add
.Caption = wks.Name
.OnAction = "'" & Me.Name & "'!someMacro"
.Tag = "someTag"
.BeginGroup = True 'between each name????
End With
End With
Next wks
End Sub

And another but...

I wouldn't want you to reset my popup toolbar. I have a personal.xla (or .xls)
that I use to modify my toolbars -- including the Cell popup.

Instead, you may want to look at this alternative from Debra Dalgleish's site:
http://contextures.com/xlToolbar01.html
And she points to an xl2007 version by Ron de Bruin:
http://contextures.com/xlToolbar01b.html

 

[Dave Peterson]

This macro has a bug in it.

Option Explicit
Sub SomeMacro()
MsgBox Application.CommandBars.ActionControl.Caption
thisworkbook.worksheets(Application.CommandBars.ActionControl.Caption) _
.select
End Sub

It'll only appear when the workbook with the code isn't the activeworkbook.

Try:

Option Explicit
Sub SomeMacro()
MsgBox Application.CommandBars.ActionControl.Caption
thisworkbook.activate '<-- added
thisworkbook.worksheets(Application.CommandBars.ActionControl.Caption) _
.select
End Sub

(but I still don't like messing up that Cell popup menu!)