Provide a range selection tool/utility to the user in Excel VBA
This control is called RefEdit control.
To use it, you have to first add it to the toolbox window by right-clicking in the toolbox window and selecting Additional Controls.... Then you select RefEdit.Ctrl and close the dialog.
Now you can select it in the toolbox and place it on your form.
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.