Prev: Pivot table field list missing
Next: Summing based on dates
From: version83 on 10 Apr 2010 12:52

Hello.
I have this code:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
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 became
active.
Something like .OnAction = Worksheet.Select or a separate macro ....
I am not sure how to do it so i need some advice.




--
version83
From: Dave Peterson on 10 Apr 2010 16:55
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



version83 wrote:
>
> Hello.
> I have this code:
>
> Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
> 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 became
> active.
> Something like .OnAction = Worksheet.Select or a separate macro ....
> I am not sure how to do it so i need some advice.
>
> --
> version83

--

Dave Peterson
From: Dave Peterson on 10 Apr 2010 16:59
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!)

Dave Peterson wrote:
>
> 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
>
> version83 wrote:
> >
> > Hello.
> > I have this code:
> >
> > Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
> > 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 became
> > active.
> > Something like .OnAction = Worksheet.Select or a separate macro ....
> > I am not sure how to do it so i need some advice.
> >
> > --
> > version83
>
> --
>
> Dave Peterson

--

Dave Peterson
 | 
Pages: 1
Prev: Pivot table field list missing
Next: Summing based on dates