Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
OnAction Variables | Excel Programming | |||
OnAction | Excel Programming | |||
OnAction | Excel Programming |