向Excel文档中嵌入VBA控件和UserForm并显示

实现环境：Visual Studio 2010, Excel 2010, VSTO 4.0

在文档中嵌入一个Commandbutton点击它会显示一个UserForm，UserForm上有一个CommandButton点击会弹出消息框然后CommandButton的Caption改变。当UserForm关闭是文档中的CommandButton的Caption改变。

VB.NET:

[vb]
Imports Microsoft.Office.Tools.Ribbon 
Imports VBE = Microsoft.Vbe.Interop 
Imports Forms = Microsoft.Vbe.Interop.Forms 
 
Public Class Ribbon1 
    Private WithEvents objCommandButton As Forms.CommandButton 
 
    Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As  _ 
                             RibbonUIEventArgs) Handles MyBase.Load 
 
    End Sub 
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e _ 
As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click 
        Dim objApplicatin As Excel.Application = Globals.ThisAddIn.Application 
        Dim objWorkbook As Excel.Workbook = objApplicatin.ActiveWorkbook 
        Dim objWorksheet As Excel.Worksheet = objWorkbook.ActiveSheet 
        Dim objShape As Excel.Shape 
        Dim objOLEObject As Excel.OLEObject 
        Dim strModuleSnippet As String 
        Dim objVBAProject As VBE.VBProject 
        Dim objVBComponent As VBE.VBComponent 
        Dim objVBFormComponent As VBE.VBComponent 
        Dim objObjectFormButton As Object 
 
        objShape = objWorksheet.Shapes.AddOLEObject("Forms.CommandButton.1") 
        objShape.Name = "btn1" 
        objOLEObject = objWorksheet.OLEObjects("btn1") 
        Try 
            objCommandButton = TryCast(objOLEObject.Object, Forms.CommandButton) 
            objCommandButton.Caption = "Click Me" 
            strModuleSnippet = "private sub btn1_Click()" & Chr(13) & _ 
                "UserForm1.Show " & Chr(13) & "end sub"