HOW CAN I WRITE A CODE TO COMMANDBUTTON ADDED AT RUNTIME?

K

Kozete

After I add a CommandButton control to a UserForm by Add method, how can I
write a code to that CommandButton?
 
J

JLGWhiz

Command buttons from the forms toolbar are attached to the macro through an
assignment dialog box at the time the marcro is created, or by calling the
dialog box at a later time, if required. These cannot be easily connected
with VBA.

Command buttons from the Control Tool box have a click event code. There
are two methods to tie the macro to the button click event. One is to put
the code directly into the click event by right clicking the button in design
mode and then click view code. This will automatically display the first and
last line of the click event macro and the user can fill in the middle part.
The second method is to put the executable code in the standard project code
module and just use the click event to call that code. For example your code
in the standard module is Macro1, this would be the code behind the command
button:

Private Sub CommandButton1_Click()
Macro1
End Sub

Macro1 would then execute from the standard module. But in any case, The
code to enact the command button must be in the command button attributes.
 
T

Tim Zych

You could theoretically write code to write code, but you will probably run
into problems quickly (virus detection may think it's a virus and
quarantines the workbook. The code persists, so it needs to be cleaned up
and rebuilt. The macro is reformed as a string, making edits a pain).

John Walkenbach has a nifty macro to do this using a class, avoiding the
problems above.

http://www.j-walk.com/ss/excel/tips/tip44.htm

To delve into this more, if you have access to it, check out Francesco
Balena's Programming Visual Basic 6. Balena's one of the best authors out
there and he has a chapter in his book devoted dynamically created forms and
events.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top