Click here to Skip to main content
Click here to Skip to main content

VS.NET Excel Addin Refedit Control

, 25 Mar 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Using a Refedit control on a VS.NET Form and Excel add-in

Introduction

This is a fully functional .NET Refedit control for use in VS.NET Excel add-ins.

Background

Having developed Excel add-in solutions for many years, the lack of a usable Refedit control on forms outside the Excel environment has been frustrating. The Refedit control included in this example provides all the functionality of the VBA Refedit solution, plus some valuable extras.

You can simply drop the control onto a VS.NET Windows form that is being used in Excel, and range selection is a simple process.

Using the Code

ExampleRefeditAddinUserForm

This example does not include an installation or compiled add-in. To test out the control, load the solution in VS.NET 2008 and click run (F5). When the add-in loads, the example form will automatically appear. You can test the control by changing the various properties on the form (specific properties are in the "refedit" category of the properties). Adding the control requires an additional step (apart from dragging it onto the form). Once a control has been added, it is necessary to assign a reference to the Excel Application to it. When instantiating the form, you need to pass Excel.Application to the constructor. The form class should look like this:

using System.Windows.Forms;

namespace ExampleUserForm
{
    public partial class Form1 : Form
    {
        private readonly Microsoft.Office.Interop.Excel.Application _excel;
        public Form1(Microsoft.Office.Interop.Excel.Application excel)
        {
            _excel = excel;
            InitializeComponent();
            refedit1._Excel = _excel;
        }
    }
}

The constructor then assigns the reference to the Excel.Application to the control(s).

Events

Additional events are included so that the program can interact with Excel.

When the Excel cell changes in the control, the CellChanged event can be called.

/// <summary>
/// The event to raise when the cell reference changes
/// </summary>
[Description("Occurs when the cell reference changes")]
public event refeditCellChangeEventHandler CellChanged;

An example of the usage of CellChanged follows:

private void refedit1_CellChanged(object sender, EventArgs e)
{
    textBox1.Text = refedit.Text;
    textBox2.Text = Excel.get_Range(refedit.Text).Value2;
}

When the User Form restores (after it has been collapsed by the control), the Restore event can be called.

/// <summary>
/// The event to raise when the refedit control restores
/// </summary>
public event refeditRestoreEventHandler Restore;

An example of the usage of Restore follows:

private void refedit1_Restore(object sender, System.EventArgs e)
{
    Button1.Focus();
}

Points of Interest

The Refedit control is still a work in progress, and the next step is to see if there is a way to automatically assign the Excel application (though I doubt there is...).

History

  • 24th March, 2009: Initial version
  • 24th March, 2009: Added the control

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

reallyBig_J

United States United States
No Biography provided

Comments and Discussions

 
QuestionVery nice.. Pinmemberholyburak11-Aug-13 1:03 
Questionhow to make it work with modal form? Pinmemberyinru hou24-Jan-13 7:52 
GeneralMy vote of 5 Pinmembergio_ch17-Jul-10 0:38 
QuestionUpdating the control? PinmemberDW196214-Apr-10 9:47 
QuestionCannot select the excel cells if the form shown in Dialog mode Pinmemberjunezhong22-Sep-09 20:01 
GeneralRe: Cannot select the excel cells if the form shown in Dialog mode Pinmemberzqsntws1-Mar-12 17:47 
GeneralRefEdit in an AddIn PinmemberPDTPGY15-May-09 1:37 
GeneralRe: RefEdit in an AddIn PinmemberreallyBig_J5-Jun-09 1:47 
QuestionHELP - your component is causing my VS 2008 Design view to crash PinmemberCSIRODeveloper4-May-09 16:43 
AnswerRe: HELP - your component is causing my VS 2008 Design view to crash PinmemberreallyBig_J5-Jun-09 4:35 
GeneralExcellent job! PinmemberKam28-Mar-09 18:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 25 Mar 2009
Article Copyright 2009 by reallyBig_J
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid