Dynamically generate multiple VBE button controls and manipulate their attributes

Source: Internet
Author: User

Implementation environment: Visual Studio 2010, Excel 2010, VSTO 4.0

[Vb]
Imports Microsoft. Office. Tools. Ribbon
Imports VBE = Microsoft. Vbe. Interop
Imports Forms = Microsoft. Vbe. Interop. Forms
 
Public Class Ribbon1
 
Private Sub ribbondomainload (ByVal sender As System. Object, ByVal e _
RibbonUIEventArgs) Handles MyBase. Load
 
End Sub
 
Private Sub button#click (ByVal sender As System. Object, ByVal e _
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

This seems to be the only method that works, because in Worksheet, we cannot operate the control attributes.


From TX_OfficeDev's column

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.