Passing arguments from a custom menu item

[gcallaway]

I'm working on adding custom menu items that control views of the worksheet. This requires tracking current view settings etc. Therefore I'd like to use a select statement to determine which menu item was selected. Can you use the action statement to pass a value such as .OnAction = "ViewSelection(1)"?

I can always send it to a sub which will then call the ViewSelection sub with the value but that seems less elegant.

Here's the code snippets:

Menu item:
With .Controls.Add(Type:=msoControlButton)
.Caption = "All"
.OnAction = "ViewSelection(1)"
.FaceId = 1098
End With

ViewSelection skeleton:
Sub ViewSelection(intOption As Integer)
Select Case intOption
Case 1
Call Show_All_Columns
Case 2
….
Case Else
…….
End Select
End Sub

Thanks in advance.

Glenn

 

[dmt32]

Hi,
You can pass argument(s) with OnAction its just a question of getting the single and double quotes right.

Give this a try & see if helps you

Sub ButtonWithArgument()
    Dim sLeft As Integer, i As Integer
MenuItem:
    sLeft = 300
    For i = 1 To 3
        With ActiveSheet.Buttons.Add(sLeft, 44.25, 72, 72)
            .Caption = "Button " & i
            .OnAction = "'ViewSelection """ & i & " '"
        End With
    sLeft = sLeft + 100
    Next

End Sub

'ViewSelection skeleton:
Sub ViewSelection(intOption As Integer)
    MsgBox intOption
End Sub

when run, code will create 3 buttons with a numeric argument assigned to pass to your procedure.

This is just an exercise but hopefully, will help you

Dave

 

[RoryA]

It doesn't really look like you need to pass an argument for what you're describing as Commandbars.Actioncontrol should return a reference to the control you triggered the routine with.