How to highlight data ranges in excel using C#?

Question

Hi, I wonder is there a way to replicate insert chart dialog functionality using C#. What I need is to display a Popup window, but more importantly to let the user to select data ranges.

I also want ranges highlighted with Excel's colorfull boxes. Here's an image of how excel highlights ranges when inserting a chart. I'd like to be able to have these boxes from my C# add-in.

Answer 1

I may be a bit outdated here, but have you tried to record a macro that does what you want?

You could then look at the generated code and adapt it as needed.

Answer 2

In VBA there is a RefEdit control that can allow you to do this, and there is a detailed walkthrough on how to use it here: How to Use the RefEdit Control with a UserForm.

There is no built-in control that can allow you to do this in C#, unfortunately. You could either use VBA as your front-end -- which I very much doubt that you'd want to do -- or you can create a RefEdit control equivalent using C#. There is an article on how to do this, along with a fully-functional C# code example, here: How to Code a RefEdit control by Gabhan Berry.

Update based on zzandy's comment:

"Actually the question is on how to highlight selected ranges, not on how to select range. The InputBox (csharp-examples.net/inputbox) is available from C# and allows to select ranges."

Getting the ranges to highlight in multi-colors will not be possible. That kind of highlighting only occurs in Excel when creating formulas on the worksheet, not when one is selecting ranges for any other purpose. The only way to create that would require an heroic amount of subclassing, or hooking, and I would not recommend it.

Some solutions, however, will allow you to at least have a marquee band around the selection (aka "marching ants"). If you use VBA as a front end, you could make use of the built-in RefEdit control, that I mentioned above, which does put the marquee band around the selected range.

The C# example I gave, above would allow you to use a RefEdit-like control within a C# form, but it would not include the marquee band when making selections -- it will simply utilize the standard look when a range is normally selected.

If you are willing to use a simple input box solution, instead of a control on a form, then that C# example you gave would work (if you use Form.Show() but not Form.ShowDialog()), but it would not show any special marquee band or other highlighting. For a simple input box that does use marquee band selection, you could make use of the Excel.Application.InputBox method, passing in a value of '8' for the 'Type', which indicates that the value returned by the 'InputBox' method should be an 'Excel.Range' data type:

Excel.Application excelApp = ...;

string prompt = "Please select the range.";
string title = "Input Range";
int returnDataType = 8;

Excel.Range myRange = excelApp.InputBox(
                          prompt,
                          title,
                          Type.Missing,
                          Type.Missing,
                          Type.Missing,
                          Type.Missing,
                          Type.Missing,
                          returnDataType)

Hope this helps...

Mike