Buttons, onAction and Parameter

[s205787]

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

 

[Harald Staff]

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

 

[Bob Phillips]

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)

 

[Rob van Gelder]

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