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

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


skrev i melding
ups.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

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)


wrote in message
ups.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