Using Excel Built-In Dialogs - Range Selection Dialog

[Tyson++]

Hi.

Im wondering if anyone knows if its possible to bring up a range selection dialog, and have the selected range returned to the VBA code. I know the code to bring up an Excel Built-In dialog:

Application.Dialogs(xlBuiltInDialog).Show(Arg1, Arg2... Arg29, Arg 30)

where xlBuiltInDialog is any member of the xlBuiltInDialog enum, and Arg1 to Arg30 are optional arguments, depending on what particular dialog you are showing.

However, I am looking for a particular dialog, and I cant seem to find it (If you didnt already know the Excel 2003 Language Reference is pretty weak). The dialog I'm talking about is used all over Excel. It is the dialog that appears whenever you click that little square box with a red arrow in it, its used on a lot of the Excel dialogs.
For an example, on the define named range dialog. Go to Insert->Name->Define. On the bottom right corner, next to the Refers To textbox, you will see the square button with the red arrow. Click it, and it minimises the define named range dialog, allowing the user to select a range, and then that range reference is returned to the textbox.

I want to display that same dialog via VBA code, and then the selected range reference be returned to my VBA code. However, even if I could find the right xlBuiltInDialog enum, I cant see how I can get a return value that contains the reference to the selected range. The Show method has a simple bool return value.

Cheers.

Tyson.

 

[parry]

Hi, this is a RefEdit control which I dont believe is available as a builtindialog. This control is available in the Control Toolbox. So you could create a form for example and place a RefEdit control.

Alternatively there is the InputBox method (not to be confused with the inputBox function) which allows you to select a data type, of which a reference is an option.

Sub Example()
Dim ReturnValue
Set ReturnValue = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

MsgBox "The cells selected were " & ReturnValue.Address
End Sub

 

[Tyson++]

Thanks for the quick response. The problem is, Im not actually coding this in VBA. I used VBA above to not overcomplicate the question.

However, I'm actually making a .NET addin for Excel. Using ComInterop I have access to the Excel object library. This means I can use all the normal VBA Excel objects and functions via wrapper classes, but I cant access the RefEdit control.

However your second approach will work, and if I cant find a better solution, I will be using it, thanks heaps! My only concern is that it doesnt tie in nicely with Excel's way of allowing users to select ranges.

The actual code behind the two dialogs seem to be different, they have slightly different behaviour, like the InputBox dialog not inserting Sheet1! if the same sheet is selected, however the other does. This isnt an issue with my implementation, it just confuses me as to why MS would code 2 seperate dialogs with similar behaviour, but not identical, unless they were keeping one for hidden within, and exposing the other via code.

Desired Dialog:

InputBox Dialog:

 

[Tyson++]

I just tried your first suggestion with the RefEdit control within Excel VBA. That is exactly what Im looking for. Now I just need to work out how to port it over to .NET... hmmm... suggestions anyone?

 

[parry]

Hi Tyson, Im afraid I have not used NET but hopefully someone can help -I would also post your question in a NET forum. A quick look in Google mentions the Visual Studio Tools for Office (application?) which appears to enable this control to be added.

regards,
Graham

 

[Legacy 98055]

Now I just need to work out how to port it over to .NET...

While you can create an instance of the RefEdit class, it must be hosted in a parent container. There is no Show method and in fact the visible method will return an error.

To add this control to a winform, from VS, simply right click the General tab in the Toolbox, select Add/Remove Items, select the COM Components tab, find and select the RefEdit control. It will now be available from your toolbox to draw on your winform. I have not used this control in VS. It is notoriously buggy in VBA.

 

[Tyson++]

Cheers Right Click. I just found the same ActiveX control myself. Only prob is it is even buggier within .NET. The **** thing wont respond once running in COM interop. It wont even draw itself!!!

Hrrmmm... I did find this though.

http://www.stochastix.de/forum/viewtopic.php?t=85

"Added Managed RefEdit control (select Excel range from a .NET GUI) "

So it must be possible to get it working.