CODEWITHSUNDEEP
vbaexcel
Vinay Pandey
Vinay Pandey

Reputation: 1179

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

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: Range Selection Icon

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.

Upvotes: 14

Views: 15873

Answers (4)

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2791

I liked @krey answer which was basically a very simplified version of the link shared in @stifin answer --> https://www.thespreadsheetguru.com/blog/vba-to-select-range-with-inputbox

But both used "odd" names for the buttons and I think guides should use default names/values for easy understand and manipulation by end user. Additionally, I added a CommandButton to "run" the userform and a "select" statement for initial testing. I also wanted to provide an initial value as I try to pre-populate my userforms, but allow end user to override if needed.

Here is the final code snipppets utilizing a UserForm w/ one TextBox named TextBox1 (default) and one CommandButton named CommandButton1 (also default).

Option Explicit

Public rng As Range

Public Sub UserForm_Initialize()
  Me.TextBox1.DropButtonStyle = fmDropButtonStyleReduce
  Me.TextBox1.ShowDropButtonWhen = fmShowDropButtonWhenAlways
  Set rng = Range("A1:B4")
  TextBox1.Value = rng.Address(False, False)
End Sub

Public Sub CommandButton1_Click()
 rng.Select
 Unload Me
End Sub

Public Sub TextBox1_DropButtonClick()
    Me.Hide
    Set rng = Application.InputBox("Select the range", "Range Picker", TextBox1.Text, Type:=8)
    TextBox1.Value = rng.Address(False, False)
    Me.Show
End Sub

Upvotes: 0

stifin
stifin

Reputation: 1400

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

Upvotes: 2

k rey
k rey

Reputation: 631

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.

Upvotes: 3

Codo
Codo

Reputation: 78825

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.

Upvotes: 14

Related Questions