Generating a new worksheet, built-in button, and event handler : OLE Objects « Data Type

Generating a new worksheet, built-in button, and event handler : OLE Objects « Data Type « VBA / Excel / Access / Word

1.	Access
2.	Application
3.	Data Type
4.	Data Type Functions
5.	Date Functions
6.	Excel
7.	File Path
8.	Forms
9.	Language Basics
10.	Math Functions
11.	Outlook
12.	PowerPoint
13.	String Functions
14.	Windows API
15.	Word
16.	XML

Microsoft Office PowerPoint 2007 Tutorial

Microsoft Office Excel 2007 Tutorial

Microsoft Office Word 2007 Tutorial

VBA / Excel / Access / Word » Data Type » OLE Objects

Generating a new worksheet, built-in button, and event handler

 

Sub AddSheetAndButton()

    Dim NewSheet As Worksheet

    Dim NewButton As OLEObject

    Set NewSheet = Sheets.Add

    Set NewButton = NewSheet.OLEObjects.Add("Forms.CommandButton.1")

    With NewButton

        .Left = 4

        .Top = 4

        .Width = 100

        .Height = 24

        .Object.Caption = "Return to Sheet1"

    End With

    Code = "Sub CommandButton1_Click()" & vbCrLf

    Code = Code & "      MsgBox ""Sheet1.""" & vbCrLf

    Code = Code & "End Sub"

    

    With ThisWorkbook.VBProject. _

      VBComponents(NewSheet.name).CodeModule

        NextLine = .CountOfLines + 1

        .InsertLines NextLine, Code

    End With

End Sub

Related examples in the same category

java2s.com |

| Contact Us | Privacy Policy

All other trademarks are property of their respective owners.