向Excel⽂档中嵌⼊VBA控件和UserForm并显⽰实现环境：Visual Studio 2010, Excel 2010, VSTO 4.0
在⽂档中嵌⼊⼀个Commandbutton点击它会显⽰⼀个UserForm，UserForm上有⼀个CommandButton点击会弹出消息框然后CommandButton的Caption改变。当UserForm关闭是⽂档中的CommandButton的Caption改变。
VB.NET:
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
vba做excel窗体录入教程
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"
'当前的VBA⼯程
objVBAProject = objApplicatin.VBE.VBProjects(0)
'当前Worksheet的Componet
objVBComponent = objVBAProject.VBComponents(0)
'加⼊代码
objVBComponent.CodeModule.AddFromString(strModuleSnippet)
'加⼀个UserForm
objVBFormComponent = objVBAProject.VBComponents.Add( _
VBE.vbext_ComponentType.vbext_ct_MSForm)
'加⼀个CommandButton
objObjectFormButton = objVBFormComponent.Designer.Controls.Add( _
"Forms.CommandButton.1")
objObjectFormButton.Caption = "Form Button"
objObjectFormButton.Name = "frmbtn1"
'由于这个Button在UserForm内所以不能像上⼀个Button⼀样直接写事件控制
'这个Button的事件必须⽤VBA代码控制
strModuleSnippet = "private sub frmbtn1_Click()" & Chr(13) & _
"Msgbox ""Hello World"" " & Chr(13) & _
"frmbtn1.Caption = ""This is a Test""" & Chr(13) & "end sub"
objVBFormComponent.CodeModule.AddFromString(strModuleSnippet)
Catch ex As Exception
MsgBox(ex.Message & Chr(13) & ex.StackTrace)
End Try
End Sub
Private Sub objCommandButton_Click() Handles objCommandButton.Click
objCommandButton.Caption = "Hello World"
End Sub
End Class
C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using VBE = Microsoft.Vbe.Interop;
using Forms = Microsoft.Vbe.Interop.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelAddIn16
{
public partial class Ribbon1
{
private Forms.CommandButton objCommandButton = null;
private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
{
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
Excel.Application objApplication = Globals.ThisAddIn.Application;
Excel.Workbook objWorkbook = objApplication.ActiveWorkbook;
Excel.Worksheet objWorksheet = objWorkbook.ActiveSheet;
Excel.Shape objShape = objWorksheet.Shapes
.AddOLEObject("Forms.CommandButton.1");
objShape.Name = "btn1";
Excel.OLEObject objOLEObject = objWorksheet.OLEObjects("btn1");
string strModuleString = string.Empty;
if (objOLEObject.Object is Forms.CommandButton)
{
objCommandButton =
(Forms.CommandButton)objOLEObject.Object;
objCommandButton.Caption = "Embedded Button";
objCommandButton.Click +=
new Forms.CommandButtonEvents_ClickEventHandler
(objCommandButton_Click);
VBE.VBProject objVBProject = objApplication.VBE.ActiveVBProject;                VBE.VBComponent objVBComponet = objVBProject.VBComponents                    .Item("Sheet1");
strModuleString = "Private Sub btn1_Click()\nUserForm1.Show\n"
+ "End Sub";
objVBComponet.CodeModule.AddFromString(strModuleString);
VBE.VBComponent objUserFormComponent =
objVBProject.VBComponents.Add
(VBE.vbext_ComponentType.vbext_ct_MSForm);
var objFormButton = objUserFormComponent.Designer.Controls.
Add("Forms.CommandButton.1");
objFormButton.Caption = "Try to Click Me";
objFormButton.Name = "frmbtn1";
strModuleString = "Private Sub frmbtn1_Click()\nMsgbox \"Hello "
+"World!\"\nfrmbtn1.Caption=\"This is a test!\"\nEnd Sub";
objUserFormComponent.CodeModule.AddFromString(strModuleString);            }
}
void objCommandButton_Click()
{
objCommandButton.Caption = "Hellow World";
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。