Excel custom form/activex control

[SKYC829]

hi @govert . it's me again.

it's possible to dynamical add or registry a form/activex control to excel?
and if it is possible how to

• add datasource to those control (e.g. Add a List<string> Type data into Combobox for users to select)
• Respond to control events (e.g. Combobox selection change event, Button click event)

[govert]

Sorry, I've not really tried or been able to figure out how to do such things. You might be able to create a WinForms control that can act as an ActiveX control in this setting, but it's going to be a tough challenge.

[SKYC829]

Sorry, I've not really tried or been able to figure out how to do such things. You might be able to create a WinForms control that can act as an ActiveX control in this setting, but it's going to be a tough challenge.

With the help of ChatGPT I found a few ways:

• add 'Microsoft.Vbe.Interop.Forms.dll' assembly references
• Get all OLE objects using the 'OLEObjects()' method of the 'Worksheet' object
• Then Add controls using the 'OLEObjects' object's add method
  For example:

using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop.Forms;
using _Application = Microsoft.Office.Interop.Excel.Application;

namespace TestAddControl;

    public class Class1
    {
        [ExcelCommand(MenuName = "A",MenuText = "B",Name ="MyCommand")]
        public static void AddControlCommand()
        {
            _Application app = (_Application)ExcelDnaUtil.Application;
            app.Workbooks.Add();
            Range cell = (Range)app.ActiveCell;
            Worksheet sheet = app.ActiveSheet;
            OLEObject obj = (sheet.OLEObjects() as OLEObjects).Add(ClassType:"Forms.CommandButton.1",Left:cell.Left,Top:cell.Top,Width:100,Height:25);
            obj.Name = "OleButton";
            obj.Object.Caption = "Shoot Me";
            ((CommandButton)obj.Object).Click += OleButton_Click;
        }

        private static void OleButton_Click()
        {
            MessageBox.Show("Yo! What's up");
        }
    }

But I think it's a little disgraceful 🤣
And I don't know why I registered COM components Winform control for using this method to add will get a 'System. AccessViolationException' exception and then lead to Excel program crashed directly 😢

[keithalewis]

+1 for @govert. -1 for ChatGPT. Looks like it hasn't been trained with Gulliver's Travels yet.

[SKYC829]

+1 对于 @govert. -1 用于 ChatGPT。 看起来它还没有接受过 Gulliver's Travels 的训练。

Interestingly ChatGPT was able to answer most of the questions about the ExcelDNA project correctly 😆
ChatGPT is an amazing thing 😆

[govert]

@keithalewis Oh no - I'm a big fan and +1 for ChatGPT! And I think it will only get better, especially as a code assistant. It's great for making these snippets for support. Of course not perfect.

@SKYC829 Can you explain a bit more of what you want - I might have misunderstood.

1. Are you trying to figure out how to insert a standard Excel ComboBox onto the sheet from a macro?
2. Or are you trying to create your own ActiveX control that will have databinding to Excel and render in its own way?

It looks like ChatGPT was kind of answering the first question, while I interpreted it as the second question of making your own type of ActiveX control.

[SKYC829]

@keithalewis Oh no - I'm a big fan and +1 for ChatGPT! And I think it will only get better, especially as a code assistant. It's great for making these snippets for support. Of course not perfect.

@SKYC829 Can you explain a bit more of what you want - I might have misunderstood.

1. Are you trying to figure out how to insert a standard Excel ComboBox onto the sheet from a macro?

2. Or are you trying to create your own ActiveX control that will have databinding to Excel and render in its own way?

It looks like ChatGPT was kind of answering the first question, while I interpreted it as the second question of making your own type of ActiveX control.

Well, my boss wants to make a form in Sheet in Excel, which contains some Combobox and Textbox. After modifying the data of Combobox and Textbox, the non-form part of Excel Sheet can obtain some data through RTD service request Api. Display the data into the rest area of this Sheet and draw some charts (such as line charts and bar charts) next to the data. I initially thought about making Combobox and Textbox components using Winform or WPF. Then for the chart part, I considered using WPF and LiveCharts to make the components of the chart part, and finally registered them in Excel in the form of ActiveX components, so that the whole Sheet could be created and used by directly clicking an ExcelCommand or ribbon control

My description may be confusing, but my boss's design is pretty much the same as th image 😜