makako
04-22-2008, 02:46 PM
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.
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
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.
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