Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Excel Macro with parameter

  1. #11
    New Member
    Join Date
    Apr 2004
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro with parameter

    I am building a command bar to aid in navigation through a workbook. Navigating between certain sheets is one thing that I want the user to be able to do. I think the combo box routing will work, but I am frustrated/fixated on this passing a parameter problem. It seems like it should be a very simple and common thing to do. The only (minor) thing I don't like about the Combo box solution you propose is the need to hit "Go". Granted, it's not that big of a deal, but it would be a bit nicer if I could get what I'm doing to work.

    Here is another site that gives a more straightforward example of the passing a parameter issue. I can't get it to work either.

    http://www.dicks-blog.com/excel/2004...g_argumen.html

    The code from that site:

    Sub SetButton()

    Dim sArg As String

    sArg = "This is a test"

    Sheet1.Shapes(1).OnAction = "'" & "ShowAnArgument """ & sArg & """'"

    End Sub

    Sub ShowAnArgument(sArg As String)

    MsgBox sArg

    End Sub

  2. #12
    New Member
    Join Date
    Apr 2004
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro with parameter

    This method seems to have solved my problem:

    http://www.microsoft.com/worddev/articles/AOV98B11.htm

    It uses the ActionControl property to pass a parameter. Seems like a round-about way of doing it, but it works...

    From the site:

    This creates the command bar:

    Private Sub Document_Open()

    CustText(0) = "click Enter "
    CustText(1) = "click OK "
    CustText(2) = "click Next "
    CustText(3) = "To do so, "
    CustText(4) = "In this article, "
    CustText(5) = "First, select "

    Dim cmbControl As CommandBarControl
    Dim cmbSubControl As CommandBarControl
    Dim X As Integer

    Application.CommandBars("text").Reset
    Set cmbControl = Application.CommandBars("Text") _
    .Controls.Add(Type:=msoControlPopup,Temporary:=True)
    cmbControl.Caption = "Custom Text"
    For X = 0 To Ubound(CustText)
    Set cmbSubControl = cmbControl.Controls.Add _
    (Type:=msoControlButton, Parameter:=X, Temporary:=True)
    With cmbSubControl
    .Caption = CustText(X)
    .OnAction = "InsertText"
    End With
    Next X
    End Sub


    This function utilized the parameter that is passed when the user uses the command bar:

    Function InsertText()
    Dim TextParam As Integer
    TextParam = CommandBars.ActionControl.Parameter
    Selection = CustText(TextParam)
    Selection.Collapse (wdCollapseEnd)
    End Function

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •