Add Code to ActiveX Control

[mllex]

I want to add a control programmatically to a new worksheet:

ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton1",Left:=72,Top:=72,Height:=20,Width:=100

Next, I want to add the following code to the button:

Load UserForm1
UserForm1.Show

Can anyone give me some tips on how to accomplish this? I tried the following, without success:

Public Sub Workbook_NewSheet(ByVal Sh As Object)

Load UserForm1
UserForm1.Show

End Sub

Thank you.

 

[SkipVought]

Hi,
After adding your commandbutton, VBA inserts the following in the sheet object:

Private Sub CommandButton1_Click()
   
End Sub

Here's where you want to insert your calls that will be executed upon the click event. Skip,
metzgsk@voughtaircraft.com

 

[mllex]

That makes sense. How can I do that in VBA?

Basically I am giving users a button which asks them questions and then produces a form for them to complete. Each new form needs to be on a new sheet, and I would like to put a command button on each sheet so that they can initiate the program again, if need be. So I need to make all of this happen when a new sheet is added.

?