动态生成多个VBE按钮控件并操作它们的属性
实现环境: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中我们是没办法操作控件属性的。
征诛志异,三让两家王朝;功同开辟,一桮万古江南。