Excel

create a button with macro from code module programmatically

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

Paleo

Description:

Insert a button in your spreadsheet and set a macro for it programmatically 

Discussion:

Sometimes you need to create a button in a spreadsheet you have imported or made some modifications and you must do it several times without knowing each sheet name. So you need a code that creates this button and its code programmaticaly. This is what I do here. 

Code:

instructions for use

			

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

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. From the Menu, choose Insert-Module.
  5. Paste the code into the right-hand code window.
  6. Close the VBE, save the file if desired.
  7. Tools | Macro | Security | Trusted Sources.
  8. Check the box for "Trust access to Visual Basic Project".
 

Test the code:

  1. Go to Tools-Macro-Macros or simply press Alt + F8.
  2. Choose "AddButtonAndCode" and then choose "Execute" or simply double-click "AddButtonAndCode".
  3. A new button will appear and if you click it you will see a message box, telling you which button you have clicked (in case you have more than one).
 

Sample File:

AddButtonAndCode.zip 8.63KB 

Approved by mdmackillop


This entry has been viewed 276 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express