Using Excel Built-In Dialogs - Range Selection Dialog

Tyson++

New Member
Joined
Jan 10, 2007
Messages
4
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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.

Code:
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++

New Member
Joined
Jan 10, 2007
Messages
4
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:
dialog1nl6.jpg


InputBox Dialog:
dialog2oy5.jpg
 

Tyson++

New Member
Joined
Jan 10, 2007
Messages
4
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 
L

Legacy 98055

Guest
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.

RefEditVbNetAddin.JPG
 

Tyson++

New Member
Joined
Jan 10, 2007
Messages
4
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.
 

Forum statistics

Threads
1,176,671
Messages
5,904,384
Members
435,089
Latest member
blackstapler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top