Form Commandbuttons generated at run-time

Hi MikeB,

If you are using Forms buttons (which I assume you are), i.e., getting the buttons from the Forms toolbar then the button can be assigned to the macro via the OnAction method of the button object. The macro must reside in a macro module, NOT in the worksheet's event code area. Here's an example of some code that puts several buttons on a worksheet, then assigns them all to a macro named colBtn_Click.

Sub GenButtons()
'
Dim cBtn As Button

'Generate buttons and assign to colBtn_Click macro

For i = 1 To 10
Set cBtn = ActiveSheet.Buttons.Add(50, 50 + 20 * i, 80, 16)
cBtn.OnAction = "colBtn_Click"
cBtn.Characters.Text = "Run Process " & i
Next i

End Sub

If you want to try the above code out, here is a simple test colBtn_Click macro to use it on. It will write the button number you click to cell G3:

Sub colBtn_Click()

' Handles all "Run Process i" button clicks

'Find out which button clicked
cBtnIndex = ActiveSheet.Buttons(Application.Caller).Index

'Write button index to cell [G3]
'(subtract 1 to account for button already on sheet)
[G3].Value = cBtnIndex - 1

Beep

End Sub

If, on the other hand, you are wanting to assign ActiveX buttons (from the Controls toolbar or toolbox) to a macro, you don't have to. These buttons will execute a click event macro whose name is always the name of the button appended with "_Click". So, if you have a macro named OK_button_Click, you must just make sure that you name name the button "OK_button" at the time you create it using the Add method. This event macro must be located in the appropriate event code location for the Userform or worksheet, whichever the button is on.

I hope this helps.

Damon

Thanks Damon,

Your advice worked perfectly for putting buttons on a worksheet and I was able to assign a pre-written macro to them as you suggested.

Unfortunately, I wasn't completely clear when I posted my problem originally and it is slightly different to the situation your suggestion successfully deals with. I am clicking a command button on a form which generates new command buttons on the same form (ie. not on a worksheet). It is these new command buttons that I cannot assign macros to. The 'OnAction' property does not seem to be recognised by buttons placed on forms during run-time.

Any ideas on what I may be missing here?

'Generate buttons and assign to colBtn_Click macro Set cBtn = ActiveSheet.Buttons.Add(50, 50 + 20 * i, 80, 16) cBtn.OnAction = "colBtn_Click" cBtn.Characters.Text = "Run Process " & i Next i cBtnIndex = ActiveSheet.Buttons(Application.Caller).Index 'Write button index to cell [G3] '(subtract 1 to account for button already on sheet) [G3].Value = cBtnIndex - 1 Beep

Mike,

The buttons on userforms are ActiveX buttons, not Forms buttons. Only Forms buttons have the OnAction property. You have to use the method I mentioned to deal with ActiveX buttons. It is different, but no more difficult than the Forms buttons, and you lose the freedom to name the routine anything you want (it has to end with "_Click".

Damon

: Hi MikeB, : ' : Dim cBtn As Button : : 'Generate buttons and assign to colBtn_Click macro : Set cBtn = ActiveSheet.Buttons.Add(50, 50 + 20 * i, 80, 16) : cBtn.OnAction = "colBtn_Click" : cBtn.Characters.Text = "Run Process " & i : Next i : cBtnIndex = ActiveSheet.Buttons(Application.Caller).Index : : 'Write button index to cell [G3] : '(subtract 1 to account for button already on sheet) : [G3].Value = cBtnIndex - 1 : : Beep : : End Sub

Hi Damon,

I'm being dumb I guess. I've tried the below, but the button I'm creating still doesn't seem to do anything.

As a test, I created a form and placed just one CommandButton on it, called CommandButton1. I've created a CommandButton1_Click routine, to generate a second button called, originally, Mike_Button. I have written a second routine called Mike_Button_Click, which just fires a message box saying "It works!". Except, it doesn't. I placed both routines together in the Userform code area. Here are the routines:

Private Sub CommandButton1_Click()

Dim myButton As Control

Set myButton = Controls.Add("Forms.CommandButton.1", "Mike_Button", Visible)
myButton.Caption = "Try this then"

End Sub

Sub Mike_Button_Click()

MsgBox "It works!"

End Sub

As I say, CommandButton1_Click works, and I have verified that the button it creates is named correctly, but the new button fails to function. Have I placed the Mike_Button_Click routine in the wrong place, or have I missed a crucial line of code somewhere?

Thanks for your ongoing help.

MikeB :

Hi again Mike,

Guess what? When it comes to adding buttons at run time to userforms, I can't get it to work either! Even the example in the VBA helps won't work in Excel 2000. Perhaps another Microsoft undocumented "feature?"

With that in mind, let me suggest what is probably a better way to do it anyway, and one that I know will work:

When you add a button at run time, you already know (when you write the code) what you want it to do, since you have to write the Click event handler ahead of time. Why not add the button to the form at design time, then make it invisible (Visible property = False). The best place to make it invisible is in the form's Initialize event. Then, when the user does whatever it is that should cause the "creation" of the button, just make it visible at that time.

Damon

Hi Damon, I'm being dumb I guess. I've tried the below, but the button I'm creating still doesn't seem to do anything. As a test, I created a form and placed just one CommandButton on it, called CommandButton1. I've created a CommandButton1_Click routine, to generate a second button called, originally, Mike_Button. I have written a second routine called Mike_Button_Click, which just fires a message box saying "It works!". Except, it doesn't. I placed both routines together in the Userform code area. Here are the routines: Private Sub CommandButton1_Click() Dim myButton As Control Set myButton = Controls.Add("Forms.CommandButton.1", "Mike_Button", Visible) myButton.Caption = "Try this then" Sub Mike_Button_Click() MsgBox "It works!" As I say, CommandButton1_Click works, and I have verified that the button it creates is named correctly, but the new button fails to function. Have I placed the Mike_Button_Click routine in the wrong place, or have I missed a crucial line of code somewhere? Thanks for your ongoing help. MikeB : Mike, : The buttons on userforms are ActiveX buttons, not Forms buttons. Only Forms buttons have the OnAction property. You have to use the method I mentioned to deal with ActiveX buttons. It is different, but no more difficult than the Forms buttons, and you lose the freedom to name the routine anything you want (it has to end with "_Click".

Damon,

Thanks for all your help on this question. Unfortunately, I can't take advantage of your suggestion, because the number of commandbuttons generated by my code varies on each occasion the macro is called, depending on the number of records of data I import earlier in the run (one button per record). So at the outset, I just don't know how many buttons I'm going to need.

Still, I have found an alternative solution, which is to create a button at design time on the form that will run a macro that parses the imported data after import, and manipulates it how I wish. Not as slick as I'd've liked, but it gets me where I want to be.

Thanks again, and all the best with your ongoing Excelling.

Mike Hi again Mike, Guess what? When it comes to adding buttons at run time to userforms, I can't get it to work either! Even the example in the VBA helps won't work in Excel 2000. Perhaps another Microsoft undocumented "feature?" With that in mind, let me suggest what is probably a better way to do it anyway, and one that I know will work: When you add a button at run time, you already know (when you write the code) what you want it to do, since you have to write the Click event handler ahead of time. Why not add the button to the form at design time, then make it invisible (Visible property = False). The best place to make it invisible is in the form's Initialize event. Then, when the user does whatever it is that should cause the "creation" of the button, just make it visible at that time.

Mike,

It doesn't matter how many commandbuttons your code might want to generate on a particular run to use the scheme I mentioned. You just need to include enough buttons at design time to cover the maximum you could ever have. The number of buttons on a form is always limited by the size of the form anyway, and you can't make it larger than the screen, so there is always an upper limit to the number of buttons. Since buttons you don't use don't appear, the user has no way of knowing whether you created the buttons at design time or at run time. In addition, your code can size the userform at run time as required to contain the number of buttons you make visible so that it looks as if the form were designed for exactly this number of buttons. I can send you an example of this if you like--just let me know.

Damon , Thanks for all your help on this question. Unfortunately, I can't take advantage of your suggestion, because the number of commandbuttons generated by my code varies on each occasion the macro is called, depending on the number of records of data I import earlier in the run (one button per record). So at the outset, I just don't know how many buttons I'm going to need. Still, I have found an alternative solution, which is to create a button at design time on the form that will run a macro that parses the imported data after import, and manipulates it how I wish. Not as slick as I'd've liked, but it gets me where I want to be. Thanks again, and all the best with your ongoing Excelling. Mike : Hi again Mike, : Guess what? When it comes to adding buttons at run time to userforms, I can't get it to work either! Even the example in the VBA helps won't work in Excel 2000. Perhaps another Microsoft undocumented "feature?" : With that in mind, let me suggest what is probably a better way to do it anyway, and one that I know will work

Damon,

OK. Could you send me your example? I'd be interested to see it. Thanks for all your help on this one,

MikeB It doesn't matter how many commandbuttons your code might want to generate on a particular run to use the scheme I mentioned. You just need to include enough buttons at design time to cover the maximum you could ever have. The number of buttons on a form is always limited by the size of the form anyway, and you can't make it larger than the screen, so there is always an upper limit to the number of buttons. Since buttons you don't use don't appear, the user has no way of knowing whether you created the buttons at design time or at run time. In addition, your code can size the userform at run time as required to contain the number of buttons you make visible so that it looks as if the form were designed for exactly this number of buttons. I can send you an example of this if you like--just let me know. , : Thanks for all your help on this question. Unfortunately, I can't take advantage of your suggestion, because the number of commandbuttons generated by my code varies on each occasion the macro is called, depending on the number of records of data I import earlier in the run (one button per record). So at the outset, I just don't know how many buttons I'm going to need. : Still, I have found an alternative solution, which is to create a button at design time on the form that will run a macro that parses the imported data after import, and manipulates it how I wish. Not as slick as I'd've liked, but it gets me where I want to be. : Thanks again, and all the best with your ongoing Excelling. : Mike