You are here:

Excel/EXCEL creating command button

Advertisement


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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2007. I am an consultant in a small defense technology services company, and have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

©2012 About.com, a part of The New York Times Company. All rights reserved.