How to add a command button to an excel sheet dynamically

Anjali
This routine will add a commandbutton and then
creat the click event code dynamically.
Sub AddComm_button()
ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Left:=126, Top:=96, Width:=126.75, Height:=25.5
Modify_CommButton
End Sub

Sub Modify_CommButton()
'===================================================================
'= Procedure: Modify_CommButton =
'= Type: Subprocedure =
'= =
'= Purpose: Add a Procedure to a module including =
'= =
'= Parameters: None =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |13-May-00| Ivan F Moala | Created =
'===================================================================

Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your Event Procedure OR SubRoutine
SubName = "Private Sub CommandButton1_Click()" & LF

'Your Procedure
Proc = "If Range(" & Ap & "A1" & Ap & " ) = 1 Then" & LF
Proc = Proc & Tabs & "MsgBox " & Ap & "Testing number =" & Ap & "& Range(" & Ap & "A1" & Ap & ")" & LF
Proc = Proc & "End If" & LF

'Use activeWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
End Sub

HTH

Ivan

Note:
You will have to obviously change the code
to suit.
The add command assumes the sheet that the activex
object is in is sheet1 hence the VBComponents("Sheet1").CodeModule code.....change to suit.
I think the code is self explanatory.....but post
if unsure.

Ivan

Hi Ivan,

Thanks for invaluable help.
Could please tell me how to change the caption
of the command button that has been created.

If possible could you please tell me how can I
know which properties are available for a
particular object.

TIA
Anjali