xlDialog to select Cell/Range

[sbitaxi]

Hello!

I was wondering if there was any way to access the dialog box Excel
uses in formulas to select a range in VBA. I want to use it for a
report tool I am developing that will search for data based on the
column header, but if it can't find the header, I want to prompt the
user to select the range containing the data.

I know how to use an InputBox for this, but they would have to type in
the cell reference manually. If they didn't know it off the top of
their head, they would have to cancel the script in order to scroll
through the spreadsheet to find the column then re-initiate it.

Any help is appreciated.


Steven

 

[Mike H]

Hi,

Use this to select your range. HdrRange will then be 'Set' and can be
referenced. The range is selected by selecting a cell or dragging to select
the range of cells and the address is automatically populated in the inputbox.

Set HdRrange = Application.InputBox("Select header", Type:=8)
MsgBox HdRrange.Address

Mike

 

[sbitaxi]

Perfect Mike, that's just what I needed. Thank you!