Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / C#

VS.NET Excel Addin Refedit Control

Rate me:
Please Sign up or sign in to vote.
4.60/5 (6 votes)
25 Mar 2009CPOL2 min read 61.6K   2.7K   16   14
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:

C#
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.

C#
/// <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:

C#
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.

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

An example of the usage of Restore follows:

C#
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)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionUso REfedit Pin
Member 131760385-Dec-19 11:09
Member 131760385-Dec-19 11:09 
QuestionUsing this in VB.net Pin
Member 1314537922-Apr-17 8:05
Member 1314537922-Apr-17 8:05 
QuestionAfter selection, where to find the data selected? Pin
joker_guy27-Feb-16 8:43
joker_guy27-Feb-16 8:43 
Thank you for your excellent work!
I am curious about where to find the data I have selected by using your method. Is there a variable or something else is holding the data? I am in urgent need about this.
Thank you very much!
QuestionVery nice.. Pin
manonthecorner_11-Aug-13 0:03
manonthecorner_11-Aug-13 0:03 
Questionhow to make it work with modal form? Pin
yinru hou24-Jan-13 6:52
yinru hou24-Jan-13 6:52 
GeneralMy vote of 5 Pin
gio_ch16-Jul-10 23:38
gio_ch16-Jul-10 23:38 
QuestionUpdating the control? Pin
DW196214-Apr-10 8:47
DW196214-Apr-10 8:47 
QuestionCannot select the excel cells if the form shown in Dialog mode Pin
junezhong22-Sep-09 19:01
junezhong22-Sep-09 19:01 
GeneralRe: Cannot select the excel cells if the form shown in Dialog mode Pin
zqsntws1-Mar-12 16:47
zqsntws1-Mar-12 16:47 
GeneralRefEdit in an AddIn Pin
PDTPGY15-May-09 0:37
PDTPGY15-May-09 0:37 
GeneralRe: RefEdit in an AddIn Pin
reallyBig_J5-Jun-09 0:47
reallyBig_J5-Jun-09 0:47 
QuestionHELP - your component is causing my VS 2008 Design view to crash Pin
CSIRODeveloper4-May-09 15:43
CSIRODeveloper4-May-09 15:43 
AnswerRe: HELP - your component is causing my VS 2008 Design view to crash Pin
reallyBig_J5-Jun-09 3:35
reallyBig_J5-Jun-09 3:35 
GeneralExcellent job! Pin
Kam28-Mar-09 17:05
Kam28-Mar-09 17:05 

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

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