动态生成多个VBE按钮控件并操作它们的属性

最新推荐文章于 2024-01-30 10:07:00 发布

勾吴江南

最新推荐文章于 2024-01-30 10:07:00 发布

阅读量1.2k

点赞数 1

分类专栏： VB.NET Office Development Excel 2010 Visual Studio 2010 文章标签： integer button string class forms excel

本文链接：https://blog.csdn.net/TX_OfficeDev/article/details/7098184

版权

Office Development 同时被 3 个专栏收录

134 篇文章 0 订阅

订阅专栏

Visual Studio 2010

40 篇文章 0 订阅

订阅专栏

Excel 2010

25 篇文章 0 订阅

订阅专栏

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

Imports Microsoft.Office.Tools.Ribbon
Imports VBE = Microsoft.Vbe.Interop
Imports Forms = Microsoft.Vbe.Interop.Forms

Public Class Ribbon1

    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 objApplication As Excel.Application = Globals.ThisAddIn.Application
        Dim objWorkbook As Excel.Workbook = objApplication.ActiveWorkbook
        Dim objWorksheet As Excel.Worksheet = objWorkbook.ActiveSheet
        Dim iButtonNumber As Integer = CInt(EditBox1.Text)
        Dim iHeight As Integer = 25
        Dim iWeight As Integer = 125
        Dim iTop As Integer = 0
        Dim iLeft As Integer = 0
        Dim objShape As Excel.Shape
        Dim objOLEObject As Excel.OLEObject
        Dim objCommandButtonControls As CommandButtonControls
        For i As Integer = 1 To iButtonNumber Step 1
            objShape = objWorksheet.Shapes.AddOLEObject _
                ("Forms.CommandButton.1", Left:=iLeft, _
                 Top:=iTop + iHeight * (i - 1), Height:=iHeight, Width:=iWeight)
            objShape.Name = "btn" & CStr(i)
            objOLEObject = objWorksheet.OLEObjects("btn" & CStr(i))
            objCommandButtonControls = New CommandButtonControls _
                (objShape.Name, "This is button" & objShape.Name)
            objCommandButtonControls.objCommandButton = TryCast _
                (objOLEObject.Object, Forms.CommandButton)
            objCommandButtonControls.Init()
        Next i
    End Sub

End Class

Public Class CommandButtonControls
    Public WithEvents objCommandButton As Forms.CommandButton
    Public Name As String
    Public Caption As String

    Public Sub New(ByVal Name As String, ByVal Caption As String)
        Me.Name = Name
        Me.Caption = Caption
    End Sub

    Public Sub Init()
        objCommandButton.Caption = Caption
    End Sub

    Private Sub objCommandButton_Click() Handles objCommandButton.DblClick
        MsgBox(Name & Chr(13) & Caption)
        If Name.Equals("btn3") Then
            objCommandButton.Font.Bold = True
        End If
    End Sub
End Class

这似乎是唯一行得通的方法了，因为在Worksheet中我们是没办法操作控件属性的。