Expert: Damon Ostrander - 10/7/2008

Question
QUESTION: Hello Mr Damon, I have some problem when creating a program in excel. i want to create a command button automatically when i still running a program that i've made in excel. this is the case:
when i input a data in a some text field in my program an after that i press some commandbutton, the command button will create another commandbutton in the same form with the caption value like the one i have typed at the text field before. is it possible to do that? thanks before.

ANSWER: Hello hilman,

I am assuming all these controls are on a userform.  While I believe it is possible to add a commandbutton while a form is running, it requires a lot of code to position it in the right place on the form, size it, assign it to run code, etc.  I suggest an easier way.

What I would recommend is to create the new commandbutton on the form, placing it exactly where you want it to appear, sizing it, etc.  Then set its Visible property to False in its property sheet. When you show the form, this button will not be visible because of this.

Once you have the (invisible) button on the form, you can use code like this in the other commandbutton's Click event code to caption it using the text in the textbox, and to make it visible:

Private Sub CommandButton2_Click()
  If TextBox1.Text <> "" Then
     CommandButton3.Caption = TextBox1.Text
     CommandButton3.Visible = True
  End If
End Sub

In this code CommandButton2 is the commandbutton that you click to make the new commandbutton (CommandButton3) appear.  The code assumes a textbox name TextBox1 on the form contains the text you want to use as a caption.  Note that this code does nothing unless there is some text in the textbox.

Keep Excelling.

Damon

---------- FOLLOW-UP ----------

QUESTION: Dear Mr Damon,
Thanks for your answer, but actually i want to make a dynamic program, so the commandbutton that will be created will be limitless (it just depends on the user). i think by making the commanbutton invisible, the program will be semi dynamic, because if all the commandbutton have already visible the user cannot create another button. I need your guidance Mr Damon. Thanks Before.

Answer
Hi again hilman,

Yes, the method I proposed would necessarily be limited, but so would a method that dynamically adds buttons to the form because eventually room for more buttons would run out on the form, and in addition a form that fills the entire screen is not particularly attractive.  Also, the form has to resize automatically as buttons are added to provide the needed space.  And the software must write the code for the buttons' Click events "on the fly", so if each button requires different code this must also be taken into account in writing the code.

If you really want to accommodate a lot of growth in the number of options, I suggest creating a listbox or combobox and adding the new options dynamically to the list. Each option could easily be made to execute code just as commandbuttons do, and the options could be named dynamically via a textbox. I believe this would be much easier to implement.  Would you like more information about how to implement this method?

Damon