Custom Context menu (Right click menu) not working in sheet changeevent.

[Madiya]

Hi all,
I face a small problem.

Following code works fine.
Sub ADD_COMMAND()
CommandBars("Cell").Reset
With Application.CommandBars("Cell").Controls
With .Add
.Caption = "OGP"
.OnAction = ThisWorkbook.Name & "OGP"
.Tag = OGPTAG
.BeginGroup = True
' .Temporary = True <<<<<< THIS LINE NOT WORKING
SO COMMENTED OUT
End With
End With
CommandBars("cell").Controls("OGP").FaceId = 44
End Sub

However, If I place the same code in worksheet activate event it does
not work.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "ALVXXL01" Then
Exit Sub
End If
CommandBars("Cell").Reset <<<<<< error out
here .>>>>>>
With Application.CommandBars("Cell").Controls
With .Add
.Caption = "OGP"
.OnAction = ThisWorkbook.Name & "OGP"
.Tag = OGPTAG
.BeginGroup = True
' .Temporary = True
End With
End With
CommandBars("Cell").Controls("OGP").FaceId = 44
End Sub

Can somebody point out the mistake I am making please.

Regards,
Madiya

 

[Jim Rech]

' .Temporary = True <<<<<< THIS LINE NOT WORKING

A control does not have a Temporary property. Rather the Controls.Add
method has a Temporary parameter:

With Application.CommandBars("Cell").Controls
With .Add(Temporary:=True)
.Caption = "OGP"
''Etc.



Use:

Application.CommandBars("Cell").Reset

in a sheet or workbook module.


--
Jim
| Hi all,
| I face a small problem.
|
| Following code works fine.
| Sub ADD_COMMAND()
| CommandBars("Cell").Reset
| With Application.CommandBars("Cell").Controls
| With .Add
| .Caption = "OGP"
| .OnAction = ThisWorkbook.Name & "OGP"
| .Tag = OGPTAG
| .BeginGroup = True
| ' .Temporary = True <<<<<< THIS LINE NOT WORKING
| SO COMMENTED OUT
| End With
| End With
| CommandBars("cell").Controls("OGP").FaceId = 44
| End Sub
|
| However, If I place the same code in worksheet activate event it does
| not work.
| Private Sub Workbook_SheetActivate(ByVal Sh As Object)
| If Sh.Name <> "ALVXXL01" Then
| Exit Sub
| End If
| CommandBars("Cell").Reset <<<<<< error out
| here .>>>>>>
| With Application.CommandBars("Cell").Controls
| With .Add
| .Caption = "OGP"
| .OnAction = ThisWorkbook.Name & "OGP"
| .Tag = OGPTAG
| .BeginGroup = True
| ' .Temporary = True
| End With
| End With
| CommandBars("Cell").Controls("OGP").FaceId = 44
| End Sub
|
| Can somebody point out the mistake I am making please.
|
| Regards,
| Madiya

 

[Madiya]

Hi Jim,
Thanks for help. Perfact.
Another small problem. I am putting this macros in an addin.
I want to show custom button only if sheet name is "ALVXXL01".
If any other sheet is activated or any other file with different sheet
name is activated/opened then this control will be deleted.
If any file having same sheet name is activated then the control will
be shown up.

Will you pl advise, how shall I proceed?

Thanks again.
Regards,
Madiya.

 

[Jim Rech]

I might suggest that a far simpler way to go is to just check whether the
right sheet is active after the user clicks the button. To make a button
visible or not dynamically from an add-in you have to raise the bar quite a
bit in terms of complexity. But it's well worth learning about this area if
you really want to advance your developer skills.

http://support.microsoft.com/kb/213566

http://www.cpearson.com/excel/AppEvent.aspx



--
Jim
| Hi Jim,
| Thanks for help. Perfact.
| Another small problem. I am putting this macros in an addin.
| I want to show custom button only if sheet name is "ALVXXL01".
| If any other sheet is activated or any other file with different sheet
| name is activated/opened then this control will be deleted.
| If any file having same sheet name is activated then the control will
| be shown up.
|
| Will you pl advise, how shall I proceed?
|
| Thanks again.
| Regards,
| Madiya.