Adding Code to a button

[TBH1]

Hey guys,

I really know nothing about vba coding. I have a simple code to run a goalseek:

Sub Macro1()
Range("A5").GoalSeek Goal:=0, ChangingCell:=Range("A4")
End Sub

I'd like to make this code run when a button created using vba code is clicked (i know how to make a button in the menus, but i don't want to use that).

I have the following code for a button (but it's also designed to create a new button everytime the code is run, which i don't want):

Sub AddButtonAndCode()
' Declare variables
Dim i As Long, Hght As Long
Dim Name As String, NName As String
' Set the button properties
i = 0
Hght = 305.25
' Set the name for the button
NName = "cmdAction" & i
' Test if there is a button already and if so, increment its name
For Each OLEObject In ActiveSheet.OLEObjects
If Left(OLEObject.Name, 9) = "cmdAction" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
If Name >= i Then
i = Name + 1
End If
NName = "cmdAction" & i
Hght = Hght + 27
End If
Next
' Add button
Dim myCmdObj As OLEObject, N%
Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
Width:=202.5, Height:=26.25)
' Define buttons name
myCmdObj.Name = NName
' Define buttons caption
myCmdObj.Object.Caption = "Click for action"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox(" & """" & "Button Clicked!" & """" & " & vbCrLf &" & _
"""" & "Put your code here!" & """" & " & vbCrLf & " & """" & "This is " & """" & _
"& " & """" & NName & """" & ")"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
End Sub

This code is clearly not exactly what i want, but it has a lot of things in it i need (like positioning the button, renaming the button etc)

Could someone please help me combine the goalseek code with the button code so that the code will run when the created button is clicked?

Oh, and one last thing. If possible, i kinda want to put the button on a different sheet to the sheet the goalseek code will be run on. (e.g. sheet 1 for button, sheet 2 for code)

Thanks heaps!

 

[adept]

Enter Design Mode (from menu bar)

Right click button and choose Assign Macro. Browse for Macro1

 

[TBH1]

Thanks for the reply. Only problem is the option to select "Assign Macro" is greyed out, anyone know why this might be?

i've also tried changing my button code to make it a lot less complex and more suited to what I want:

Private Sub cmdMakeButton_Click()
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=50, Top:=50, _
Width:=100, Height:=100)
Dim cmd As CommandButton
Set cmd = obj.Object
obj.Name = "Initiate Calculation"
obj.Select
End Sub

Again, my lack of VBA knowledge prevents me from linking the goalseek code with the button - and *** the "assign macro" route doesn't appear to be an option due to greying out, i'm at a loss. Help would be greatly appreciated once more.

I tried linking the goalseek code to the button code via:

Private Sub CommandButton1_Click()
Range("Calculations!B32").Goalseek Goal:=0, ChangingCell:=Range("Calculations!F34")
End Sub

But that doesn't work. I get a run time error