Assign command to CommandButton using VBA Code

[vodkasoda]

I am creating details on a Userform within VBA code as follows :

For A = 1 To MyUniqueNo
    MyLeft = 14
    UserForm3c.Frame1.ScrollBars = fmScrollBarsVertical
    UserForm3c.Frame1.ScrollHeight = UserForm3c.Frame1.Height * (MyUniqueNo / 18)
    Set MyButton = UserForm3c.Frame1.Controls.Add("Forms.CommandButton.1")
    With MyButton
        .Caption = A
        .Width = 42
        .Height = 18
        .Left = MyLeft
        .Top = MyTop
    End With
    MyLeft = MyLeft + 42
Next
UserForm3c.Show

The screen works fine, but I now want to add a Command to each CommandButton that is created in this loop, just to display a new screen, but I can't figure out what to use. I tried

.OnAction = "Call Update_Player"

but I get a "Run-Time error 438, Object doesn't support this property or method" ...

How can I do this ?

Just had a thought, should I be creating xxx_Click routines for each button (or even 1 for any button & passing the parameters) ? If so, what do I call it ... what are my CommandButtons called ... oh dear, that looks like a stupid question ?!?!?!?

 

[venkat1926]

this line may be necessary somewhere in the beginning

Application.CommandBars("Forms").Visible = True

and then on action code try this

.OnAction = "Update_Player"

post feedback

 

[vodkasoda]

It makes no difference, I'm afraid ...

I put some test MsgBoxes in & discovered that my CommandButtons are being named logically, CommandButton1, CommandButton2 etc., but I have scoured the Internet & cannot find a way to assign an Action to these Command Buttons. In fact it seems I am not the only person having this problem ...

I have found a way of doing it if the UserForm is temporary (http://scriptorium.serve-it.nl/view.php?sid=13) but I can't seem to tweak this so that the Command Buttons work when they are added to a UserFrom that has been created using the design tool.

Surely there is somebody on here that knows how to do something that, on the face of it, looks so simple ?!?!?

 

[venkat1926]

my CommandButtons are being named logically, CommandButton1,

see this code

Application.CommandBars("Forms").Visible = True
    ActiveWindow.SmallScroll Down:=4
    ActiveSheet.Buttons.Add(588, 164.25, 138, 24.75).Select
Selection.Name = "create_graph"

the cruicial line is

Selection.Name = "create_graph"

this will give a name to the button instead of default namae like commandbutton1 etc by excel.

 

[vodkasoda]

Again, no luck ... I can call the Command Buttons anything I like, but if I pre- create _Click event subroutines, both in the UserForm code and in the executing Module code, it takes no effect ... very frustrating indeed !!!

see this code

Application.CommandBars("Forms").Visible = True
    ActiveWindow.SmallScroll Down:=4
    ActiveSheet.Buttons.Add(588, 164.25, 138, 24.75).Select
Selection.Name = "create_graph"

the cruicial line is

Selection.Name = "create_graph"

this will give a name to the button instead of default namae like commandbutton1 etc by excel.

 

[venkat1926]

try google searach

 

[venkat1926]

did you create button from forms toolbar or control x toolbar?