none
OPENING THE VBA EDITOR ON CLICK ON OLEOBJECTS BUTTON RRS feed

  • Question

  • Hey,

    in my excel plugin , i have dynamically generated the OLEOBJECTS Textbox ..

    when i click on them to write something . THEY opens up the

    Private Sub cmdActionTxt0_Change()

    End Sub
     VBA editor

    i do not want it to be opened and simply want to write over them.

    How is that possible.

    Wednesday, December 14, 2011 12:40 PM

Answers

  • Hi ArchanaSighvi,

    According to your dispection, I changed my snippet:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Xml.Linq;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.Office.Tools;
    using System.Windows.Forms;
    using VBE = Microsoft.Vbe.Interop;
    
    namespace ExcelAddIn14
    {
        public partial class ThisAddIn
        {
            private bool flag = false;
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                Excel.Workbook objWorkbook = Application.ActiveWorkbook;
                Excel.Worksheet objWorksheet = objWorkbook.ActiveSheet;
                Excel.Shape objShape = objWorksheet.Shapes.AddOLEObject("Forms.TextBox.1");
                objShape.Name = "textBox1";
                var objOELObject = objWorksheet.OLEObjects("textBox1");
                if (objOELObject.Object is VBE.Forms.TextBox)
                {
                    VBE.Forms.TextBox objTextBox = (VBE.Forms.TextBox)objOELObject.Object;
                    objTextBox.Change += new VBE.Forms.MdcTextEvents_ChangeEventHandler(objTextBox_Change);
                }
            }
    
            void objTextBox_Change()
            {
                if (!flag)
                {
                    Excel.Application objApplication = Application;
                    VBE.VBE objVBE = objApplication.VBE;
                    objVBE.MainWindow.Visible = true;
                    flag = true;
                }
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            #region VSTO generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
            
            #endregion
        }
    }
    <br/>
    

    I hope it can help you.

    If I have misunderstood anything, please feel free to let me know.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, December 20, 2011 10:46 AM
    Moderator
  • Hi ArchanaSinghvi,

    I would recommend you use TryCast in VB.NET.

    Let's have a test.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 9:46 AM
    Moderator

All replies

  • Hi ArchanaSighvi,

     

    Thanks for posting in the MSDN Forum.

     

    Would you please clarify your issue? I think you want to embed a textbox in Excel document and when you write some in the particular textbox it will fire a change event. Is it right?

     

    If it is, this snippet will help you out.

    using System.Windows.Forms;
    using System.Xml.Linq;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.VisualStudio.Tools.Applications.Runtime;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using MF = Microsoft.Vbe.Interop.Forms;
    
    namespace ExcelWorkbook7
    {
        public partial class ThisWorkbook
        {
            private MF.TextBox textBox1 = null;
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                Excel.Workbook objWorkbook = Application.ActiveWorkbook;
                Excel.Worksheet objWorksheet = objWorkbook.ActiveSheet;
                var objTextBox = objWorksheet.Shapes.AddOLEObject("Forms.TextBox.1",
    Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 2, 2, 100,
    20);
                objTextBox.Name = "TextBox1";
                Excel._OLEObject objOLEObject = objWorksheet.OLEObjects("TextBox1");
                
                if (objOLEObject.Object is MF.TextBox)
                {
                    textBox1 = (MF.TextBox)objOLEObject.Object;
                    textBox1.Change +=
                        new MF.MdcTextEvents_ChangeEventHandler(textBox1_Change);
                }
    
            }
    
            void textBox1_Change()
            {
                MessageBox.Show(textBox1.Text);
            }
    
            private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            #region VSTO Designer generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisWorkbook_Startup);
                this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
            }
    
            #endregion
    
        }
    }
    

     

     

     

    I hope what I said can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 16, 2011 4:01 AM
    Moderator
  • Hi Tom,

    i am creating EXCEl 2010 add in plugin that is Application level project..

    as in EXCEL in built features , when we drag a textbox on excel , we simply write on it .

    it does not open up the VBA editor.

    i want same thing to be happened in my plugin... i click on the RIBBON Button and on evry click it generates new textBOX .

    i just simply want to select and strat writing text.

    i do not want it to opene VBA editor.

    in APLICATION LEVEL PROJECT there is no WORKBOOK START UP EVENTS..

    now tell me how not to show the VBA EDITOR when i strat typing text into textboxes.


    i would like to clear you the things  ..This issue is also with MY dynamic generated buttons ..

    please see the code that i have written for generating buttons dynamically...

     

     act = Globals.ThisAddIn.Application.ActiveSheet
            ' Declare variables
            Dim i As Long = 0, Hght As Long = 27
            Dim Name As String, NName As String
            'Dim oleObject As Excel.OLEObject
            ' Set the button properties
            Hght = 305.25
            ' Set the name for the button
            NName = "cmdAction" & i
            ' Test if there is a button already and if so, increment its name
            Dim entry As KeyValuePair(Of String, Microsoft.Vbe.Interop.Forms.CommandButton)
            If listOLEControls.Count > 0 Then
                For Each entry In listOLEControls
                    If Microsoft.VisualBasic.Left(entry.Key, 9) = "cmdAction" Then
                        Name = Microsoft.VisualBasic.Right(entry.Key, Len(entry.Key) - 9)
                        If Name >= i Then
                            i = Name + 1
                        End If
                    End If
                Next
                Hght = entry.Value.Top + 27
            End If
            NName = "cmdAction" & i
            ' Add button
            Dim myCmdObj As Microsoft.Office.Interop.Excel.OLEObject
            myCmdObj = act.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
              Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
              Width:=102.5, Height:=26.25)
            ' Define buttons name
            myCmdObj.Name = NName

            ' Define buttons caption
            '    myCmdObj.Object.Caption = "Click for action"
            'myCmdObj.d
            myCmdObj.Object.Caption = NName


            btn = myCmdObj.Object
            '   btn.DesignMode = True

            AddHandler btn.Click, AddressOf buttonClickEvent

            AddHandler btn.MouseMove, AddressOf btn_MouseMove

            listOLEControls.Add(NName, btn)
            btn = Nothing
                myCmdObj = Nothing
            Catch ex As Exception

            End Try

    When i try to chnage the text of the button , it opens up the VBA editor.

    Please see the code and tell how to rectify it..

    this can be a help for TEXTBOX ALSO..


    This code is written in RIBBON.vb as i want to fire the event on RIBBON's button CLICK.

    i have used  Microsoft.Vbe.Interop.Forms.CommandButton as i want to customize their properties in my own way.

    and as we are maintaining their IDs , i can really do this...

    please consider these things when you reply..

     

     

    Friday, December 16, 2011 4:53 AM
  • Hi ArchanaSighvi,

    According to your dispection, I changed my snippet:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Xml.Linq;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.Office.Tools;
    using System.Windows.Forms;
    using VBE = Microsoft.Vbe.Interop;
    
    namespace ExcelAddIn14
    {
        public partial class ThisAddIn
        {
            private bool flag = false;
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                Excel.Workbook objWorkbook = Application.ActiveWorkbook;
                Excel.Worksheet objWorksheet = objWorkbook.ActiveSheet;
                Excel.Shape objShape = objWorksheet.Shapes.AddOLEObject("Forms.TextBox.1");
                objShape.Name = "textBox1";
                var objOELObject = objWorksheet.OLEObjects("textBox1");
                if (objOELObject.Object is VBE.Forms.TextBox)
                {
                    VBE.Forms.TextBox objTextBox = (VBE.Forms.TextBox)objOELObject.Object;
                    objTextBox.Change += new VBE.Forms.MdcTextEvents_ChangeEventHandler(objTextBox_Change);
                }
            }
    
            void objTextBox_Change()
            {
                if (!flag)
                {
                    Excel.Application objApplication = Application;
                    VBE.VBE objVBE = objApplication.VBE;
                    objVBE.MainWindow.Visible = true;
                    flag = true;
                }
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            #region VSTO generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
            
            #endregion
        }
    }
    <br/>
    

    I hope it can help you.

    If I have misunderstood anything, please feel free to let me know.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, December 20, 2011 10:46 AM
    Moderator
  • hey

    i am trying to write this line in vb.net

       if (objOELObject.Object is VBE.Forms.TextBox)

    VB.NET CODE

     

    If (myCmdObj1.Object Is Microsoft.Vbe.Interop.Forms.TextBox)

    Then

     

    End

    If

    but it is saying thet TEXTBOX is a Type in FORMs and can not be used as an expresions.

     

     

     

    Tuesday, December 20, 2011 12:58 PM
  • Hi ArchanaSinghvi,

    I would recommend you use TryCast in VB.NET.

    Let's have a test.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 9:46 AM
    Moderator