当前位置:软件学习 > Excel >>

向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" 
            '当前的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#:

[csharp]
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 ob

补充:Web开发 , ASP.Net ,
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,