Create a button and asign Macro from VBA

[makako]

Hi, I have an addin that creates a new worksheet with simple formulas but I want to create a button that creates a GoalSeek Action.
The button is created correctly and in the right place but the Caption and the OnAction sentence is showing me errors. Also, when excecuted I want the button to run the SubAction macro. Still, the code for SubAction will be in the addin so if some one knows how to copy the code from SubAction to Private Sub CommandButton1_Click() would be great.

[VBA]
sub CreateButton
Set lvObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
With lvObject
.Height = Range("G1:G2").Height
.Width = Range("G1:G2").Width
.Top = Range("G1:G2").Top
.Left = Range("G1:G2").Left
.OnAction = "SubAction"
.Caption = "Calculate"
End With
End Sub

Sub SubAction()
On Error Resume Next
Range("F4").GoalSeek Goal:=0, Changingcell:=Range("F2")
If Err.Number <> 0 Then MsgBox "Error!"
On Error GoTo 0
End Sub
[/VBA]

[lucas]

Can I offer an alternative to creating objects on the fly?

Since you are creating the new worksheet from your addin, why not have a menu item that is loaded with the addin and can run macro's in the addin.

Then you could run your SubAction macro on any active sheet. If I understood, this macro is already in your addin, right?

you could add your create new sheet macro to the menu too.....

[lucas]

Link to the menu template that I use. It makes it easy to create and maintain your menu's. When done just save it as an addin make sure to reference activeworkbook in all of your code in an addin as thisworkbook will reference your addin instead of the sheet you wish to manipulate.

Edit: I forgot the link. http://j-walk.com/ss/excel/tips/tip53.htm

[makako]

The macro is quite heavy and I prefer it to be independent. Although I had thought about it and the alternative was to insert the sheet with the button inside the addin and just copy when needed.
Sheet1.copy
This work for me but I was wondering if any Guru around knew a way of creating it "on fly" for this and other purposes.
Still, thanks. Comment appreciated!


Ps: Ive created menus thanks to the j-walk code but this is not the best way out here.

[lucas]

If the new sheet you are creating is identical each time then another option would be to create the new sheet from a template that could be a hidden sheet. button could already be there.

read this about creating controls on the fly before you decide.
http://www.vbaexpress.com/forum/showthread.php?t=11177

[makako]

Ok, understood, jijiji, no fans of "on fly controls" around. then ill go with the sheets1.copy option. Thanks anyway

ps: Is there any creating and event code thread with enought info just to take a peek?

Thanks

[lucas]

Here's a couple. Use the search function and search for programatically

http://vbaexpress.com/kb/getarticle.php?kb_id=274

http://www.vbaexpress.com/forum/show...rogramatically

[rory]

There are some good examples of VBProject manipulation on Chip Pearson's website.