none
Want to Capture the Name of the OLE OBJECTS BUTTON on DoubleCLICK /Mouse-Down event and want to set It's properties like Color , fonts etc RRS feed

  • Question

  • Hi,

    I have one requirement in which i have dynamically created oleobjects button  through COMMAND BUTTON CLASS. Now what i want is i want to change their basic properties like backColor, Font etc through Modal form.

    so i want to handle their DoubleClick event where i can get the Name of the buttons that be unique and then i can set the properties.

    the thing is how can i get the Ids/Name of each button that is double clicked. as there is no argument for it.

    I have written this code for dynAMICALLY GENERATING BUTTONS:

      Sub AddButtonAndCode()
            Try

         
            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.MouseDown, AddressOf btn_MouseDown

     

                AddHandler btn.MouseMove, AddressOf btn_MouseMove


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

            End Try
        End Sub

    If i try to capture the Name of the Button through Button (Integer) argument , it do not give me the correct name of button,

    It gives me the name of First button among several dynamic buttons. 

    IS IT POSSIBLE TO SET THEIR PROPERTIES THROUGH MODAL FORM?

    Tuesday, December 20, 2011 12:51 PM

Answers

  • Hi ArchanaSignhvi,

     

    Thanks for posting in the MSDN Forum.

     

    I noticed that you post several thread which regard to VBE controls there is : http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/3475dc64-20d0-47b0-9edd-0d6f5c467b1b/#3475dc64-20d0-47b0-9edd-0d6f5c467b1b, http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/72b3b278-c584-45d6-aa92-812574edf520/#b376e2e4-6583-4584-9da4-d854ee3b5289, http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/de21ab5d-40a8-4606-a63e-71bddea903ce/#de21ab5d-40a8-4606-a63e-71bddea903ce. These threads have one main goal that you want to access the controls properties is it right?

     

    It’s based on my experience that create a customized class to wrap VBE control and attach the event in that class if you want to access them via VB.NET. And it seems the only way to modify the VBE controls which embedded in the Worksheet.

     

    The following snippet will explain what I said. The same way can be used in C# too.

    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
    

     

     

     

    If you feel it can help you please mark my reply as answer in the threads which I mentioned in this post.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 23, 2011 6:38 AM
    Moderator

All replies

  • Hi ArchanaSignhvi,

     

    Thanks for posting in the MSDN Forum.

     

    I noticed that you post several thread which regard to VBE controls there is : http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/3475dc64-20d0-47b0-9edd-0d6f5c467b1b/#3475dc64-20d0-47b0-9edd-0d6f5c467b1b, http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/72b3b278-c584-45d6-aa92-812574edf520/#b376e2e4-6583-4584-9da4-d854ee3b5289, http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/de21ab5d-40a8-4606-a63e-71bddea903ce/#de21ab5d-40a8-4606-a63e-71bddea903ce. These threads have one main goal that you want to access the controls properties is it right?

     

    It’s based on my experience that create a customized class to wrap VBE control and attach the event in that class if you want to access them via VB.NET. And it seems the only way to modify the VBE controls which embedded in the Worksheet.

     

    The following snippet will explain what I said. The same way can be used in C# too.

    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
    

     

     

     

    If you feel it can help you please mark my reply as answer in the threads which I mentioned in this post.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 23, 2011 6:38 AM
    Moderator
  • Thanx Tom,

    I will check it out soon

    Monday, December 26, 2011 5:12 AM
  • Hi ArchanaSinghvi,

     

    Any update?

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 27, 2011 5:14 AM
    Moderator
  • Hey Tom

    Thnx for ur useful post..but i m busy in another POC for time being so will update u whenever i got time to try this.

    Tuesday, December 27, 2011 5:21 AM
  • Hi ArchanaSinghvi,

     

    I will close this thread, it you feel it can’t help you, please feel free to unmark it.

     

    If you have new problems, please create a new thread for your issue.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Friday, January 6, 2012 9:24 AM
    Moderator