+ Reply to Thread
Results 1 to 4 of 4

[SOLVED] Buttons, onAction and Parameter

  1. #1
    s205787@student.windesheim.nl
    Guest

    [SOLVED] Buttons, onAction and Parameter

    Hi,

    I am trying to create some buttons on my sheet, that execute a macro on
    click. I want to add a parameter or a reference to the button somehow.
    The code I have is the following:


    With ActiveSheet.Buttons.Add(194, (rownr) * 12.75 - 5, 13, 13)
    ..Characters.Text = "+"
    ..Name = artikel
    ..OnAction = "mymacro"
    ' .Parameter = name 'this does not work
    End With


    now in this mymacro i have the following code:


    sub mymacro()
    ' name = CommandBars.ActionControl.Name 'this does not work
    ' name = Selection.name ' this does not work either


    I am a little confused by the differences between the different ways of
    creating buttons (buttons.add, shapes.add, oleobjects.add,
    commandbars.controls.add)
    I don't know which command to use and why. The sheet is created on the
    fly and creates a given number of buttons based on other data. What I
    need to do is to fill in some data when the buttons are clicked, and
    create a combobox somewhere.

    Thanks in advance


  2. #2
    Harald Staff
    Guest

    Re: Buttons, onAction and Parameter

    Hi

    There are buttons and there are buttons, as you already know. But you are
    very close; the macro called will recognize the button name with
    Application.Caller. Try this:

    Sub tester()
    Dim rownr As Long
    rownr = 5

    With ActiveSheet.Buttons.Add(194, (rownr) * 12.75 - 5, 13, 13)
    ..Characters.Text = "+"
    ..Name = "Plus button"
    ..OnAction = "mymacro"
    End With
    rownr = rownr + 1
    With ActiveSheet.Buttons.Add(194, (rownr) * 12.75 - 5, 13, 13)
    ..Characters.Text = "-"
    ..Name = "Minus button"
    ..OnAction = "mymacro"
    End With

    End Sub

    Sub mymacro()
    MsgBox Application.Caller
    End Sub

    HTH. Best wishes Harald


    <s205787@student.windesheim.nl> skrev i melding
    news:1106125687.355433.75650@c13g2000cwb.googlegroups.com...
    > Hi,
    >
    > I am trying to create some buttons on my sheet, that execute a macro on
    > click. I want to add a parameter or a reference to the button somehow.
    > The code I have is the following:
    >
    >
    > With ActiveSheet.Buttons.Add(194, (rownr) * 12.75 - 5, 13, 13)
    > .Characters.Text = "+"
    > .Name = artikel
    > .OnAction = "mymacro"
    > ' .Parameter = name 'this does not work
    > End With
    >
    >
    > now in this mymacro i have the following code:
    >
    >
    > sub mymacro()
    > ' name = CommandBars.ActionControl.Name 'this does not work
    > ' name = Selection.name ' this does not work either
    >
    >
    > I am a little confused by the differences between the different ways of
    > creating buttons (buttons.add, shapes.add, oleobjects.add,
    > commandbars.controls.add)
    > I don't know which command to use and why. The sheet is created on the
    > fly and creates a given number of buttons based on other data. What I
    > need to do is to fill in some data when the buttons are clicked, and
    > create a combobox somewhere.
    >
    > Thanks in advance
    >




  3. #3
    Bob Phillips
    Guest

    Re: Buttons, onAction and Parameter

    Parameter applies to commandbar buttons, and you are not creating this type
    of button, but a shapes button. Thus you can not add a parameter or access
    such.

    These buttons are a little limited in what you can pass. AFAIK there are no
    properties that you can tap into, but you could always use a worksheet range
    and test that in the macro.

    If you are using 1 macro for many buttons, you could use Application.Caller
    to get the name of the button initiating the macro.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    <s205787@student.windesheim.nl> wrote in message
    news:1106125687.355433.75650@c13g2000cwb.googlegroups.com...
    > Hi,
    >
    > I am trying to create some buttons on my sheet, that execute a macro on
    > click. I want to add a parameter or a reference to the button somehow.
    > The code I have is the following:
    >
    >
    > With ActiveSheet.Buttons.Add(194, (rownr) * 12.75 - 5, 13, 13)
    > .Characters.Text = "+"
    > .Name = artikel
    > .OnAction = "mymacro"
    > ' .Parameter = name 'this does not work
    > End With
    >
    >
    > now in this mymacro i have the following code:
    >
    >
    > sub mymacro()
    > ' name = CommandBars.ActionControl.Name 'this does not work
    > ' name = Selection.name ' this does not work either
    >
    >
    > I am a little confused by the differences between the different ways of
    > creating buttons (buttons.add, shapes.add, oleobjects.add,
    > commandbars.controls.add)
    > I don't know which command to use and why. The sheet is created on the
    > fly and creates a given number of buttons based on other data. What I
    > need to do is to fill in some data when the buttons are clicked, and
    > create a combobox somewhere.
    >
    > Thanks in advance
    >




  4. #4
    Rob van Gelder
    Guest

    Re: Buttons, onAction and Parameter

    The following is based on the CommandBar example on my website.


    In the Workbook Code Module:

    Private Sub Workbook_Activate()
    Toolbar_ON
    End Sub

    Private Sub Workbook_Deactivate()
    Toolbar_OFF
    End Sub


    In a standard Code Module:

    Const cCommandBar = "MyCommandBar"

    Sub Toolbar_OFF()
    Dim bar As CommandBar

    ''' Delete the Commandbar if it already exists
    For Each bar In Application.CommandBars
    If bar.Name = cCommandBar Then bar.Delete
    Next
    End Sub

    Sub Toolbar_ON()
    Dim bar As CommandBar

    Toolbar_OFF

    Set bar = Application.CommandBars.Add(Name:=cCommandBar,
    Position:=msoBarTop, Temporary:=True)

    ''' Button 1
    With bar.Controls.Add(Type:=msoControlButton)
    .FaceId = 136
    .Caption = "Click Me 1"
    .TooltipText = "Click here for a Message Box"
    .Style = msoButtonIconAndCaption
    .Parameter = "My Button 1"
    .OnAction = "Button_Click"
    End With

    ''' Button 2
    With bar.Controls.Add(Type:=msoControlButton)
    .FaceId = 136
    .Caption = "Click Me 2"
    .TooltipText = "Click here for a Message Box"
    .Style = msoButtonIconAndCaption
    .Parameter = "My Button 2"
    .OnAction = "Button_Click"
    End With

    ''' Toggle
    With bar.Controls.Add(Type:=msoControlButton)
    .FaceId = 134
    .Caption = "Toggle Me"
    .Style = msoButtonIconAndCaption
    .State = msoButtonUp
    .OnAction = "Toggle_Click"
    End With

    ''' Edit
    With bar.Controls.Add(Type:=msoControlEdit)
    .OnAction = "Edit_Enter"
    End With

    ''' Dropdown
    With bar.Controls.Add(Type:=msoControlDropdown)
    .AddItem "Newton"
    .AddItem "Galileo"
    .AddItem "Einstein"
    .DropDownLines = 15
    .DropDownWidth = 100
    '.ListHeaderCount = 1
    .ListIndex = 3
    .OnAction = "Dropdown_Click"
    End With

    ''' Combo
    With bar.Controls.Add(Type:=msoControlComboBox)
    .AddItem "Processor"
    .AddItem "Hard Drive"
    .AddItem "Memory"
    .AddItem "Graphics"
    .DropDownLines = 15
    .DropDownWidth = 100
    '.ListHeaderCount = 1
    .ListIndex = 2
    .OnAction = "ComboBox_Click"
    End With

    ''' Popup
    With bar.Controls.Add(Type:=msoControlPopup)
    .Caption = "Cards"
    With .CommandBar.Controls.Add(Type:=msoControlButton)
    .FaceId = 481
    .Caption = "Heart"
    .OnAction = "Popup_Click"
    End With
    With .CommandBar.Controls.Add(Type:=msoControlButton)
    .FaceId = 482
    .Caption = "Diamond"
    .OnAction = "Popup_Click"
    End With
    With .CommandBar.Controls.Add(Type:=msoControlButton)
    .BeginGroup = True
    .FaceId = 483
    .Caption = "Spade"
    .OnAction = "Popup_Click"
    End With
    With .CommandBar.Controls.Add(Type:=msoControlButton)
    .FaceId = 484
    .Caption = "Club"
    .OnAction = "Popup_Click"
    End With
    End With

    bar.Visible = True
    End Sub

    Sub Button_Click()
    With Application.CommandBars.ActionControl
    MsgBox "You clicked the button!" & vbNewLine & _
    "The Parameter is " & .Parameter
    End With
    End Sub

    Sub Toggle_Click()
    With Application.CommandBars.ActionControl
    If .State = msoButtonUp Then
    .FaceId = 135
    .State = msoButtonDown
    Else
    .FaceId = 134
    .State = msoButtonUp
    End If
    End With
    End Sub

    Sub Edit_Enter()
    With Application.CommandBars.ActionControl
    MsgBox "You typed: " & .Text
    End With
    End Sub

    Sub Dropdown_Click()
    With Application.CommandBars.ActionControl
    MsgBox "You selected: " & .Text
    End With
    End Sub

    Sub ComboBox_Click()
    With Application.CommandBars.ActionControl
    MsgBox "You selected: " & .Text
    End With
    End Sub

    Sub Popup_Click()
    With Application.CommandBars.ActionControl
    MsgBox "You selected: " & .Caption
    End With
    End Sub


    --
    Rob van Gelder - http://www.vangelder.co.nz/excel


    <s205787@student.windesheim.nl> wrote in message
    news:1106125687.355433.75650@c13g2000cwb.googlegroups.com...
    > Hi,
    >
    > I am trying to create some buttons on my sheet, that execute a macro on
    > click. I want to add a parameter or a reference to the button somehow.
    > The code I have is the following:
    >
    >
    > With ActiveSheet.Buttons.Add(194, (rownr) * 12.75 - 5, 13, 13)
    > .Characters.Text = "+"
    > .Name = artikel
    > .OnAction = "mymacro"
    > ' .Parameter = name 'this does not work
    > End With
    >
    >
    > now in this mymacro i have the following code:
    >
    >
    > sub mymacro()
    > ' name = CommandBars.ActionControl.Name 'this does not work
    > ' name = Selection.name ' this does not work either
    >
    >
    > I am a little confused by the differences between the different ways of
    > creating buttons (buttons.add, shapes.add, oleobjects.add,
    > commandbars.controls.add)
    > I don't know which command to use and why. The sheet is created on the
    > fly and creates a given number of buttons based on other data. What I
    > need to do is to fill in some data when the buttons are clicked, and
    > create a combobox somewhere.
    >
    > Thanks in advance
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1