13

I'm working in Excel on a VBA project, and want part of my macro to prompt the user to select a range of cells*, which the macro can later do stuff with.

*The type of prompt you get when creating a chart, or using a GUI to insert a function

e.g. here: Screengrab showing required functionality - the Excel 'Sum' Function Arguments pop-up

and here: Screengrab showing required functionality - the Excel Function Arguments pop-up

I'm therefor looking for something along the lines of

Sub MyMacro()
    MsgBox "Please select data range"

    ' allow user to select range (as images above)
    CreateFunctionArgumentsPrompt()    

    'do stuff with user selected range of cells
    ...
End Sub

Is it possible to access built-in Excel functionality to perform what I refer to as: CreateFunctionArgumentsPrompt()

Note: this is similar to SO question excel vba get range of user selected range by mouse but differs in that

  1. I want to use the built in GUI functionality of Excel as displayed above
  2. I need to be able to select and refer to a range on a sheet other than the active worksheet
Community
  • 1
  • 1
Jonny
  • 3,807
  • 8
  • 31
  • 48
  • I found this link: http://www.excelforum.com/excel-programming-vba-macros/493780-excel-range-choose-button-in-vba-form.html This may be helpful – lowak Apr 02 '14 at 13:18

2 Answers2

34

This isn't using the built in that you showed above, but does allow you to select a range of cells following an income prompt:

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

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

This is based on the answer given in this MrExcel answer.

Here is how it looks in use:

enter image description here

ChrisProsser
  • 12,598
  • 6
  • 35
  • 44
  • 3
    There is only one problem with this code. The range address that is returned is not fully qualified if the selection is from a different sheet or different workbook. So you will have to take care of that as well :) You might want to see the link that I posted below the question... – Siddharth Rout Apr 02 '14 at 13:42
  • 1
    This is awesome! Just what I was looking for, thanks! – Mr. Meeseeks Sep 22 '16 at 13:02
  • 1
    @Siddharth Rout — I just tested this solution and both `rng.Worksheet.Name` and `rng.Worksheet.Parent.Name` (to check the workbook) returned the sheet and workbook names even when I select a range on a different sheet or workbook. – ChrisB Jul 18 '18 at 23:26
  • 1
    Way easier to read `Type:=8` vs `,,,,,,,,8)` – FreeSoftwareServers Nov 19 '19 at 11:03
0

What you are looking for is a dialog box (also called a common dialog). Unfortunately you cannot add one to the existing built in objects (at least not using VBA).

As mentioned above you can mimic this functionality using InputBox and Forms. That said I have seen proprietary programs that are based on Excel where the company added the type of functionality you describe. However, I believe you have to use C++ or a deeper language to create DLLs that can accomplish this

One thing worth noting about dialogs: Excel has a built in Common File Dialog object library which allows you to create common file server dialog boxes (such as Open, Save & Select) using existing Windows API dialogs.