Creating, Formatting and Adding Code to a New ActiveX Command Button

[Broomey]

Hi All,

Pretty new to VBA, but trying to do somthing a little bit tricky. I have a model whereby a simulation is performed and if the user wants to keep it, they can save it to another worksheet and then run another simulation in the original worksheet. What I want to do is be able to add a new command button into th new worksheet created when the simulation is saved. I want this button to be captioned "Delete Saved Simulation" and to be vbRed in colour. When this button in the new sheet is clicked, I want the worksheet to delete itself. I've had a bit of a play around with it already and while the simulation save all works, creating, coding and formatting the new command button is proving to be difficult.

Any help would be greatly appreciated!

The code I have so far for this is:
Sub Button()
Dim Obj As OLEObject
Dim CodeMod As Object
Dim Code As String
Dim Line As Integer
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=1.5, Top:=92.25, Width:=153, Height:=42 _
).Select
ActiveSheet.CommandButton1.Caption = "Delete Saved Simulation"
ActiveSheet.CommandButton1.BackColor = vbRed
Set CodeMod = Activeworksheet.vbprject.vbcomponenets(ActiveSheet.CodeName).codemodule
With CodeMod
Code = "Private Sub Command_Button1_Click()" & vbCrLf
Code = Code & "Call DeleteSim(""" & Sname & """)" & vbCrLf
Code = Code & "End Sub"
.insertlines Line, Code
End With
End Sub

 

[Norie]

Does it need to be an ActiveX button?

If it was a Forms button you could assign a macro to it rather than having to write the code for the button to the VBE.

 

[Broomey]

Hi Norie,

I have no idea, as long as it works then I'm cool with it. How would that work then??