Excel: How do you use RefEdit/range selection control to select a cell range using .Net
In Excel VBA, we could use RefEdit control on a user form for allowing users to select a cell range on worksheets. This control allows users to select a range easily just by dragging on Excel cells and it can be passed as an input (as range object) to the program. It is very beneficial for users while doing Excel programming using VBA, as user does not require typing the address of a range manually.
However, we cannot use this control in other than VBA userforms, because RefEdit is not really a true ActiveX control, it is a special in-process to Excel only control that directly integrates with VBA. As per the following article, the RefEdit Control is designed to work only when placed on a form in an Excel VBA project.
RefEdit control does not work on form in a Com Add-In in Excel
As a workaround, some people develop their own user control to achieve this functionality in Excel. However, if we want to use an Excel out of box option to implement such functionality in our .Net applications or any external Excel automation applications, here is what we need to do,
We could make use of Application.Inputbox method that Excel Object Model exposes with the Type argument (which is last argument) set as 8. This allows us to select a cell range, as if we select while using RefEdit control/Function Arguments dialog box, which we get when we insert Excel functions
I have implemented this using the C# code snippet below,
Excel.Application xlApp = "<Refer to an Excel application instance>"; //Declares range object Microsoft.Office.Interop.Excel.Range rng; //Implements InputBox method with last argument value is set as 8 rng = (Microsoft.Office.Interop.Excel.Range)xlApp.InputBox("Select Range", System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, 8); //Display the range address that we just received as an input using InputBox method MessageBox.Show(rng.get_Address(System.Type.Missing, System.Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing));