none
Use .NET ActiveX buttons as Excel OLE Objects RRS feed

  • Question

  • Hi,

    I am trying to develop an ActiveX library in vb.net that contains a user control that is used as Excel ole object buttons. I am creating the buttons as Excel ole objects, programmatically (i.e. in VBA) through the following code in a macro Sub:

    'Start of macro

    Dim o as OleObject
    Dim DimCtrl as ActiveX.DimCtrl       'DimCtrl is a control in .NET ActiveX.dll class library
    Dim cell as Range

    'Delete all the existing button controls before creating them.
    If ActiveSheet.OLEObjects.Count > 0 Then

     For Each o In ActiveSheet.OLEObjects

      If o.ProgID = "ActiveX.DimCtrl" Then

       o.Delete

      End If

     Next

    End If

    Set cell = ActiveSheet.Cells(1, 1)
    Set o = ActiveSheet.OLEObjects.Add(ClassType:="<some_class_type>", Link:=True, DisplayAsIcon:=False, Left:=cell.Left, Top:=cell.Top, Width:=cell.Width, Height:=cell.Height)

    If Not o Is Nothing Then
     Set DimCtrl = o.Object
     DimCtrl.ClassIndex = 1
     DimCtrl.ClassName = "Entity"
     o.LinkedCell = cell.Address
     o.Placement = xlMove
     o.Activate
    End If

    'End of macro

    I tried creating .Net class library project and added a user control named "DimCtrl". The objective was to this user control as buttons as Excel ole objects.

    The following are the questions:
    1. Not sure what to value should I use for "ClassType" parameter in "OLEObjects.Add()" method?
    2. Cannot assign cell address to the value of "LinkedCell" property.

    Can someone please provide an example of how to go about it? I hope, I have clarified my queries. Many thanks in advance.

    Kind regards,
    Misbah



    Wednesday, August 23, 2017 10:29 AM

All replies

  • Hi Misbah Masood-ul-Hasan,

    I understand that you are developing class library project.

    in that you had added the user control.

    what makes me confuse is ,"user control that is used as Excel ole object buttons. I am creating the buttons as Excel ole objects, programmatically (i.e. in VBA)".

    user control used as buttons or you want to add the buttons to user control that you created using VBA?

    what is the overall idea or goal?

    if possible, then can you change your project type to VSTO Addin? in that also you can use user control and you can try to use Excel object model using interop.

    is it necessary to create ole buttons in VBA or we can use any other controls provided by the Visual studio.

    if you provide some information then we can try to understand the issue in better way and try to provide you some suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 24, 2017 12:42 AM
    Moderator
  • Hi Deepak,

    Thank you very much for your reply.

    The intension is to have an empty user control (with no controls on it) in a .Net class library project and then use it as somewhat like Active X buttons on an Excel sheet. The instances of this user control (in .Net class library) are created programmatically by a macro (in VBA) - the code snippet already mentioned in my previous post.

    I have been able to do this in VB 6.0 where I created OCX, added reference to this OCX in my VBA project module and created instances of this OCX as OLE objects on Excel worksheet through a macro.

    The reason for doing all this exercise is because our original ActiveX buttons developed in an ATL-based library (in VC++) are crashing in Excel 2016 only and work well up till Excel 2013. Please go to the following link to have a look at my other question in order to get the background and reason for choosing the exercise as an alternative to the problem:

    https://social.msdn.microsoft.com/Forums/en-US/a06a5c1d-f9d6-4df3-9985-86535af63d14/atlbased-activex-crashing-in-excel-2016?forum=exceldev

    I hope, I have clarified.

    As suggested, that is exactly what I am going to try now to create a VSTO add-in (which is a COM Add-In) and I'll update accordingly. Meanwhile, please suggest, if you come up with any suggestions. Many thanks.

    Kind regards,

    Misbah

    Thursday, August 24, 2017 8:56 AM
  • Hi Misbah Masood-ul-Hasan,

    let us know about your testing with VSTO Addin.

    whether it worked for you or not.

    so that we can try to provide you further suggestions if needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 25, 2017 9:16 AM
    Moderator
  • Hi Deepak,

    Sorry for the late reply, I was busy doing other higher priority tasks. Also, thank you very much for following up.

    I was in the middle of trying with VSTO Add-in. I added a user control to this add-in to use this user control as an Excel OLE object on an Excel sheet. I added a reference to this add-in in the VBA project module. However, I got stuck at a line of code in a VBA macro that creates OLE objects. The following is the block of code in a (VBA) macro:

        Dim o As OLEObject
        Dim TCMDim As ActiveXTest.TCMDimCtrl
        Dim cell As Range
        
        'Delete all the existing button controls before creating them.
        If ActiveSheet.OLEObjects.Count > 0 Then
            For Each o In ActiveSheet.OLEObjects
                If o.progID = "ActiveXTest.TCMDimCtrl" Then
                    o.Delete
                End If
            Next
        End If

        Set cell = ActiveSheet.Cells(1, 1)
        Set o = ActiveSheet.OLEObjects.Add(ClassType:="ActiveXTest.TCMDimCtrl", Link:=True, DisplayAsIcon:=False, Left:=cell.Left, Top:=cell.Top, Width:=cell.Width, Height:=cell.Height)
        
        If Not o Is Nothing Then
            Set TCMDim = o.Object
            TCMDim.ClassIndex = 1
            TCMDim.ClassName = "Entity"
            o.LinkedCell = cell.Address
            o.Placement = xlMove
            o.Activate
        End If

    Please refer to the line of code, marked bold. If I use "ClassType" parameter in OLEObjects.Add(...) function then I get an error as "cannot insert object". If I use "Filename" parameter and pass path to the VSTO add-in as a value then, the code fails at the following line:

    o.LinkedCell = cell.Address

    I do not want to make significant changes in the macro, as it works up to Excel 2013. The objective is just to replace the ATL class library with the alternate class library as long as this fixes the crash, happening in Excel 2016 only.

    I hope, I am not confusing you and have clarified the problem and objective.

    Kind regards,

    Misbah

    Thursday, September 7, 2017 8:54 AM
  • Hi Misbah Masood-ul-Hasan,

    can you try to upgrade this code VSTO instead of using VBA and try to use VSTO Controls?

    with that also you can create buttons on the sheet.

    if by doing that you can able to fulfil your requirement then here you can move your code to VSTO.

    I have a little confusion for what it will be used.

    because VBA and VSTO both share the same object model.

    so the same thing you can perform with any one of two.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 8, 2017 1:47 AM
    Moderator
  • Hi Deepak,

    Thank you very much for your reply and for following up.

    I already tried converting the code from VBA to VB.NET, but I am experiencing the same problem, as mentioned in my last post. Below is the part extracted from my last post (in bold):

    Please refer to the line of code, marked bold. If I use "ClassType" parameter in OLEObjects.Add(...) function then I get an error as "cannot insert object". If I use "Filename" parameter and pass path to the VSTO add-in as a value then, the code fails at the following line:

    o.LinkedCell = cell.Address

    Therefore, I am having the same problem in VB.NET as well.

    Referring to my last post, I do not want to disturb or make significant changes in the VBA macro, as it is our standard code that has been there for more than 10 years and that has worked well up to Excel 2013. The VBA code with ATL-based control library still works in Excel 2016. It is just that, the controls are crashing Excel 2016 when closing/exiting Excel. That was why, I thought, of replacing ATL-based controls with .NET-based to see, if that would have fixed the crashing problem, but then I landed up on other problems.

    I hope, I have clarified. Any suggestion?

    Kind regards,

    Misbah

    Monday, September 11, 2017 9:01 AM
  • Hi Misbah Masood-ul-Hasan,

    I can see that issue is little complex.

    so I will try to involve senior engineers to look in to this issue and they try to provide you suggestions.

    that can solve your issue.

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 12, 2017 2:57 AM
    Moderator
  • Thank you very much, Deepak. Yes, this is a strange one, as to why the crashing occurs in Excel 2016 whereas, the things are ok up till Excel 2013. Both Excel 2013 and 2016 designs are same, that is, they are both based on SDI (Single-document Interface).

    Kind regards,

    Misbah

    Thursday, September 14, 2017 2:31 PM
  • Hello Misbah,

    As mentioned above, I would also suggest moving to VSTO to achieve it.

    If you have would like to troubleshoot the Excel crash issue with the ATL based controls that you have, I would suggest to paid support. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    Regards,

    Ajay

    Monday, October 2, 2017 9:43 PM