OnAction command failure

[peter.bostrom]

I use Excel 2000. My problem is that the following code
works only on my computer. I have tested the same code
on five different Excel 2000 computers but it works only
for me. The Tools>options, Tools>VBA project properties,
tools>digital signature and tools>references
settings are the same for all (five) computers.
Any help is most appreciated.

Sub DropDownMenu5()
Set NewMenu = MenuBars(xlWorksheet).Menus.Add
(Caption:="ID Filter", Before:="Help")
Set MenuItemAdded = MenuBars(xlWorksheet).Menus("ID
Filter").MenuItems.Add(Caption:="1",
OnAction:="'DataFiltera 1'")
Set MenuItemAdded = MenuBars(xlWorksheet).Menus("ID
Filter").MenuItems.Add(Caption:="2",
OnAction:="'DataFiltera 2'")
End Sub

Sub DataFiltera(Kriteria As Integer)
Select Case Kriteria
Case 1
Sheets("Sheet1").Range("A2") = "value 1"
Case 2
Sheets("Sheet1").Range("A2") = "value 2"
End Select
End Sub

 

[Paul D]

can you be more specific about what does not work? Error messages received?
etc.
Paul

 

[Tom Ogilvy]

Suspect the other 5 computers have the lastest service release for xl2000.
I believe the lack of support for the undocumented feature of setting an
argument in an onaction string began in the lastest SR for xl2000.

 

[Tom Ogilvy]

It fails when he presses the menu button because of this:

OnAction:="'DataFiltera 2'"

 

[Jake Marx]

Hi Peter,

As Tom noted, passing arguments via the OnAction property is not fully
supported. Instead of using a parameter in your Subroutine, you can use the
Parameter property of the CommandBarButton. You can get this property from
within the DataFiltera subroutine with:

Application.CommandBars.ActionControl.Parameter

But in order to do this, you'll have to switch to using CommandBars instead
of Menus. Here's (what should be) a working example:

Sub DropDownMenu5()
Dim nHelp As Integer

nHelp = Application.CommandBars(1).Controls("Help").Index
With Application.CommandBars(1).Controls.Add( _
Type:=msoControlPopup, before:=nHelp, temporary:=True)
.Caption = "ID Filter"
With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.Caption = "1"
.OnAction = "DataFiltera"
.Parameter = "1"
End With
With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.Caption = "2"
.OnAction = "DataFiltera"
.Parameter = "2"
End With
End With
End Sub

Sub DataFiltera()
Dim Kriteria As Integer

Kriteria = CInt(Application.CommandBars.ActionControl.Parameter)

Select Case Kriteria
Case 1
Sheets("Sheet1").Range("A2") = "value 1"
Case 2
Sheets("Sheet1").Range("A2") = "value 2"
End Select
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]