Embed and display the VBA control and UserForm in the Excel document

Source: Internet
Author: User

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

Embed a Commandbutton in the document and click it to display a UserForm. A CommandButton clicking on UserForm will pop up a message box, And the CommandButton Caption will change. When UserForm is disabled, the CommandButton Caption in the document is changed.

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 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 _
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"
'Current VBA Project
ObjVBAProject = objApplicatin. VBE. VBProjects (0)
'Current Worksheet Componet
ObjVBComponent = objVBAProject. VBComponents (0)
'Add code
ObjVBComponent. CodeModule. AddFromString (strModuleSnippet)
'Add a UserForm
ObjVBFormComponent = objVBAProject. VBComponents. Add (_
VBE. vbext_ComponentType.vbext_ct_MSForm)
'Add a CommandButton
ObjObjectFormButton = objVBFormComponent. Designer. Controls. Add (_
"Forms. CommandButton.1 ")
ObjObjectFormButton. Caption = "Form Button"
ObjObjectFormButton. Name = "frmbtn1"
'The event control cannot be written directly like the previous Button because the Button is in UserForm.
'The Button event must be controlled using VBA code.
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 ribbonupload load (object sender, RibbonUIEventArgs e)
{
 
}
 
Private void button#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 ";
}
}
}


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.