first of all: beginner on vba , don't have clue how userforms works.
that said trying assing code 3 dynamically created commandbuttons.
after research come across this page , wrote code write codes of buttons. problem is, need distribute workbook approach not anymore.
i reaserched lot (1, 2, 3, 4) , came across this post. tried example @siddharthrout did not sucessfull. tried understand how classmodule works couldn't (1, 2). think code 1 @siddharthrout solve problem can't manage make work on normal module.
long story short: need code assing codes commandbuttons without using extensibility (code writes code).
edit
want create these buttons on normal sheet, not on userform.
read this: http://scriptorium.serve-it.nl/view.php?sid=13
sub makeform() dim tempform object ' vbcomponent dim formname string dim newbutton msforms.commandbutton dim textlocation integer ' ** additional variable dim x integer 'locks excel spreadsheet , speeds form processing application.vbe.mainwindow.visible = false application.screenupdating = false ' create userform set tempform = thisworkbook.vbproject.vbcomponents.add(vbext_ct_msform) 'set properties tempform tempform .properties("caption") = "temporary form" .properties("width") = 200 .properties("height") = 100 end formname = tempform.name ' add commandbutton set newbutton = tempform.designer.controls _ .add("forms.commandbutton.1") newbutton .caption = "click me" .left = 60 .top = 40 end ' add event-hander sub commandbutton tempform.codemodule ' ** add/change next 5 lines ' code adds commands/event handlers form x = .countoflines .insertlines x + 1, "sub commandbutton1_click()" .insertlines x + 2, "msgbox ""hello!""" .insertlines x + 3, "unload me" .insertlines x + 4, "end sub" end ' show form vba.userforms.add(formname).show ' ' delete form thisworkbook.vbproject.vbcomponents.remove vbcomponent:=tempform end sub 
Comments
Post a Comment