EXCEL VBA: calling an event with onclick property of a button which is being created on the fly

Question

I am creating a excel VBA userform which requires many buttons to be created on the fly (ie as the requirement may be, runtime). I want to assign a subroutine to these buttons so that when they are clicked a subroutine should execute.

this is what I want to do

Set obj4 = usrform.Controls.Add("forms.commandbutton.1")
obj4.Height = 17
obj4.Left = 450
obj4.Top = 75 
obj4.Font.Size = 11
obj4.Name = "compare" & (j - i) 'j and i are some variables in the code
obj4.Caption = "Compare!"
obj4.onclick =  abcd

public sub abcd()
'some code here
end sub

but this code is not running. I read somewhere that here I cannot call a subroutine but a function. My problem is that I want a subroutine only as I dont intend to get something in return from it. But still for trial purpose I made abcd() a function in the above code and then my code was running but unsuccessfully as it was executing the function abcd without the need of button getting pressed.

Can anyone help me with this? I have searched a lot for this on internet in various forums.

Try `obj4.onclick = "abcd"`. There are some more suggestions here: http://stackoverflow.com/q/566770/18771 — Tomalak, Mar 13 '13 at 08:20
http://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically-using-vba-on-excel — Daniel Dušek, Mar 13 '13 at 09:46

score 1 · Answer 1 · answered Mar 13 '13 at 08:29

Don't get hung up on subroutine v. function: if you don't set the return value of a function, it's the same as a subroutine.

When you set the onClick property, you give it the name of the function as a string obj4.onclick = "abcd" otherwise it will execute the function and save the return value.

EXCEL VBA: calling an event with onclick property of a button which is being created on the fly

1 Answers1