Fancy way of returning an Excel range selection

bigvarn

New Member
Joined
Nov 15, 2005
Messages
4
Hi,

I'm trying to find out about the fancy way that Excel allows you to select a range. This functionality appears in a number of places in Excel.

To better understand what I mean, create a chart, then right-click on it and select "Source Data..." (you may have to click around to invoke the correct menu).

You'll see a picture of your chart, with its data range underneath. To the right of that data range is a button that will collapse the form and allow you to select a range. How do you recreate that functionality in a VBA form?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You can use the inputbox method. It doesn't allow the box to be collapseable, but you can still select and use a range:

Code:
Sub test()
Dim myRng As Range

On Error Resume Next
Set myRng = Application.InputBox("Select a range", Type:=8)

'if cancel button is pressed, code will exit
If myRng Is Nothing Then Exit Sub

MsgBox "You have selected " & myRng.Address

End Sub
 

bigvarn

New Member
Joined
Nov 15, 2005
Messages
4
Not a bad solution at all!

That said, to be able to mimic Excel's built-in method would give it a seriously professional look.

Anyone? Anyone? Bueller?
 

bigvarn

New Member
Joined
Nov 15, 2005
Messages
4
I've subsequently found that the functionality I'm after is provided by a control: refedit. Which is nice.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,082
Messages
5,570,095
Members
412,311
Latest member
Mozz
Top