MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to add a command button to an excel sheet dynamically

Posted by Anjali on November 27, 2000 1:20 PM


Anybody could you please help me out.....How to add a command button
dynamically through code and how to access its click event through

Please help me out urgently

Thanks in advance

Posted by Ivan Moala on November 28, 2000 12:53 AM

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
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



Posted by Ivan Moala on November 28, 2000 1:10 AM

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.


Posted by Anjali on November 29, 2000 2:18 PM

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.