Click here to Skip to main content
11,717,509 members (82,167 online)
Click here to Skip to main content

VS.NET Excel Addin Refedit Control

, 25 Mar 2009 CPOL 32.6K 1.8K 15
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

You may also be interested in...

Comments and Discussions

 
QuestionVery nice.. Pin
holyburak11-Aug-13 0:03
memberholyburak11-Aug-13 0:03 
Questionhow to make it work with modal form? Pin
yinru hou24-Jan-13 6:52
memberyinru hou24-Jan-13 6:52 
GeneralMy vote of 5 Pin
gio_ch16-Jul-10 23:38
membergio_ch16-Jul-10 23:38 
QuestionUpdating the control? Pin
DW196214-Apr-10 8:47
memberDW196214-Apr-10 8:47 
QuestionCannot select the excel cells if the form shown in Dialog mode Pin
junezhong22-Sep-09 19:01
memberjunezhong22-Sep-09 19:01 
GeneralRe: Cannot select the excel cells if the form shown in Dialog mode Pin
zqsntws1-Mar-12 16:47
memberzqsntws1-Mar-12 16:47 
GeneralRefEdit in an AddIn Pin
PDTPGY15-May-09 0:37
memberPDTPGY15-May-09 0:37 
GeneralRe: RefEdit in an AddIn Pin
reallyBig_J5-Jun-09 0:47
memberreallyBig_J5-Jun-09 0:47 
QuestionHELP - your component is causing my VS 2008 Design view to crash Pin
CSIRODeveloper4-May-09 15:43
memberCSIRODeveloper4-May-09 15:43 
AnswerRe: HELP - your component is causing my VS 2008 Design view to crash Pin
reallyBig_J5-Jun-09 3:35
memberreallyBig_J5-Jun-09 3:35 

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
Web03 | 2.8.150901.1 | Last Updated 25 Mar 2009
Article Copyright 2009 by reallyBig_J
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid