Trouble assigning macro(args) to dynamically created buttons

[2me]

This surely isn't the first time someone is asking about this, but after searching for a solution for 4 hours, I think it's time to ask...

Simplistically I'm supposed to do a group of commandbuttons (50-500, amount varies), all of withch should be assigned to a systematically different kind of macro (every button shows a different chart). I've tried to pass a argument through the OnAction- command, but it just doesn't work. I've tried a code like this:


Sub FormsButton_Add()
For i = 1 To 10 '//I'm trying first with 10 buttons
ActiveSheet.Buttons.Add(150, 50 + 50 * i, 50, 50).Select
Sheet1.Shapes(i).OnAction = " ' macro1(" & i & ") ' "
Next i
End Sub

Public Sub macro1(i As Integer)
'code for showing chart number (i)
End Sub

Problem is, that Excel doesn't find the macro1, because I'm messing with the arguments. Without arguments everything goes.

I'm having xl2000 with VBA 6.0

 

[Norie]

Why do you have single quotes (') ?

Sheet1.Shapes(i).OnAction = "macro1(" & i & ")"

By the way why do you have all these buttons, surely there's a better solution, perhaps using a combobox or listbox.

Or getting input from the user.

 

[Juan Pablo González]

Try this:

Sub FormsButton_Add()
Dim i As Long

For i = 1 To 10 '//I'm trying first with 10 buttons
   With ActiveSheet.Buttons.Add(150, 50 + 50 * i, 50, 50)
      .OnAction = "'macro1 " & i & "'"
   End With
Next i
End Sub

Public Sub macro1(i As Integer)
'code for showing chart number (i)
   MsgBox i
End Sub

If you need to pass strings, then careful with the quotes. Look at this post from **** Kusleika:

http://tinyurl.com/9p33g

 

[Juan Pablo González]

Why do you have single quotes (') ?

Sheet1.Shapes(i).OnAction = "macro1(" & i & ")"

...

Norie, that causes a Runtime error 1004, Unable to set the OnAction property of the Button class.

 

[Norie]

Juan

You're right - I never tested and just wondered why the single quotes where there.

 

[Glory]

Found this after posting this: http://www.mrexcel.com/forum/showthread.php?t=549818

Juan's suggestion (to place single quotes inside the double quotes) also raises the same error.