Provide a range selection tool/utility to the user in Excel VBA

Question

I am trying to develop a user form in Excel 2007 VBA and want to provide a user with a range selection icon, something like this:

However, I have not been able to find any built-in form design tool or any online tool which provides this or at least gives me an idea. If anyone has any idea about this, I will greatly appreciate their help.

Answer 1

Although this question is already almost a decade old, it still came up as my first Google search result so I'm going to post an answer as another approach to consider. The InputBox with type set to cell reference might be sufficient for many people's needs. The InputBox type does the drudge work of validating the user's response. See this article for how to use the InputBox types: https://www.thespreadsheetguru.com/blog/vba-to-select-range-with-inputbox

Answer 2

Another alternative to using the RefEdit.Ctrl is to hook into some undocumented features of the TextBox control and use the Application.InputBox function.

There are two properties of the TextBox control that do not appear in the Properties dialog, that allow you to add a button on the right. They are DropButtonStyle and ShowDropButtonWhen. When the button is clicked it will fire the DropButtonClick event for the control where you can show the input box.

Start by placing a TextBox control on the form. Then add the following to the UserForm_Initialize procedure:

Private Sub UserForm_Initialize()
    txtRefersTo.DropButtonStyle = frmDropButtonStyleReduce
    txtRefersTo.ShowDropButtonWhen = frmShowDropButtonWhenAlways
End Sub

Then add an event handler to the DropButtonClick event as follows to capture the range using the Application.InputBox dialog:

Private Sub txtRefersTo_DropButtonClick()
    Me.Hide
    txtRefersTo.Text = Application.InputBox("Select the range", "Range Picker", txtRefersTo.Text, Type:=8)
    Me.Show vbModal
End Sub

The main advantage to this approach is that it allows you to place a control within a frame or on a separate tab without experiencing the issues associated with the RefEdit.Ctrl. The disadvantage is that it requires a separate dialog to interact with Excel.