How to ask user for a cell/range selection?

Jan 21, 2014 at 7:02 PM
I'm trying to write some CTPs and/or Forms to provide custom data import into Excel sheets and can't figure out a way to ask the user to select a destination for the data.

Ideally it would be something similar to the built-in control that Excel uses which minimizes the current form to allow a cell/range selection.

Any pointers as to how to do this would be greatly appreciated!

Thanks,

~Ethan
Coordinator
Jan 21, 2014 at 7:39 PM
Hi Ethan,

One approach is to use the Excel C API to build a dialog. This is fairly limited, and if you're not familiar with the C API will take some figuring out. But you'll get the best range selection control that way. The starting point is to find the C API reference help file here: http://www.xceed.be/Blog.nsf/dx/excel-macro-function-help-file-for-windows-7 and then look for the DIALOG.BOX function. You'll call this from your Excel-DNA add-in as XlCall.Excel(XlCall.xlcDialogBox, ...) (in a macro context, so you might need ExcelAsyncUtil.QueueAsMacro if you're in a ribbon handler or other form). This discussion gives a small example: https://groups.google.com/forum/#!topic/exceldna/U6glMmn98KU Mapping the parameters to an Excel-DNA call should be straight-forward.

Another approach is to use a special range selection control in a Windows Forms dialog. You can check these posts:
http://blogs.msdn.com/b/gabhan_berry/archive/2008/06/12/net-refedit-control.aspx
http://www.breezetree.com/blog/index.php/excel-refedit-in-c-sharp/
http://www.codeproject.com/Articles/32805/RefEdit-Emulation-for-NET

I haven't tried any of these approaches.

Good luck,
Govert
Sep 29, 2014 at 7:21 PM
Hi Ethan,
Did you manage to implement any of suggested solutions?

Thanks,
Alex
Sep 29, 2014 at 7:31 PM
Alex,

Sorry but I never did find a solution and abandoned the concept altogether in my project.

I played around with the first range selection control suggested by Govert but wasn't happy with how it was working. I also tried calling the DIALOG.BOX function and again while that was also functional it didn't feel great as each time a cell reference was need an extra dialog box would pop up.

Good luck,

~Ethan
Oct 2, 2014 at 9:45 AM
Thank for your reply.
If by any chance you still have the code for DIALOG.BOX solution through ExcelDNA call, can you post it or send me an example?

Regards,
Alex
Oct 7, 2014 at 11:50 PM
Tanhe wrote:
Alex,

Sorry but I never did find a solution and abandoned the concept altogether in my project.

I played around with the first range selection control suggested by Govert but wasn't happy with how it was working. I also tried calling the DIALOG.BOX function and again while that was also functional it didn't feel great as each time a cell reference was need an extra dialog box would pop up.

Good luck,

~Ethan
Hi Tanhe,

I think i must be missing something here (probably) but what about an alternate solution to ""asking the user to select a destination for the data""... Would it not be possible for you to pop UP your own built Dot Net Modal Form that asks the user to ENTER the starting Row and starting Column and the ending row and the ending column of their data destination... something like...

http://www.dbniche.com/RangeAreaForDataImport.jpg

and they input their area range and you grab the variables and then import - not graphic but it should work ??

Maybe not exactly this but something like it ??

Just a thought...

cj