Range selection tool/wizard/dialog 1

[CautionMP]

Has anyone run across a built in routine in Excel that will allow you to prompt a user to select a range from a workbook/worksheet and return the range object to a macro?

I'm looking for something similar to button that is available in the Formula Palette that lets you select a range of cells from a worksheet and returns the range as test to the Formula Palette.

I have done some digging in the Help files and cannot find (a.k.a. don't know) what I'm looking for and as usual I have a short deadline.

Thanks in advance for any help.

 

[Zathras]

If you know VBA, it's easy enough to create your own form to do this. Create a form and add a list box and two command buttons. Then put this code in the form code page:

Option Explicit

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  SelectedRange = ListBox1.Value
  Unload Me
End Sub

Private Sub CommandButton1_Click()
  SelectedRange = ListBox1.Value
  Unload Me
End Sub

Private Sub CommandButton2_Click()
  SelectedRange = ""
  Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
Dim sRange As String
  For i = 1 To ActiveWorkbook.Names.Count
    ListBox1.AddItem (ActiveWorkbook.Names(i).Name)
  Next i
End Sub

And put this code in a code module:

Option Explicit
Public SelectedRange As String

Sub test()
  UserForm1.Show
  If SelectedRange > "" Then
    MsgBox SelectedRange
  Else
    MsgBox "Cancel"
  End If
End Sub

Then run the macro (F5 from the VBA editor or Alt-F8 from the worksheet.)

You may want to insert additional code to limit which range names appear in the list box -- that's up to you.

 

[CautionMP]

Zathras,

Thank you for your response. This is not quite what I'm looking for.

I already have a user form that prompts the for data that will be changed in the source system, once this form is loaded I need the user to be able to point back to the workbook and define the range of 'records' that the need to be enumerated through.

My goal is to allow the user to change the range on the fly so they can select the entire group of records, a subset of the records, or an individual record.

CMP

 

[CautionMP]

Because a picture is worth a thousand words, here is a sample that roughly does what I'm trying to do (but in an ugly way).

Here is the form code (one button, one text box)

Private Sub CommandButton1_Click()
  Dim rngCurrent As Range
  Set rngCurrent = ActiveCell
  With rngCurrent
    .Formula = "=RangeOCells(A1)"
    .Activate
  End With
  Application.Dialogs(xlDialogFunctionWizard).Show
  TextBox1.Value = ActiveCell.Value
End Sub

And here is the supporting function

Public Function RangeOCells(DataRange As Range) As String
  RangeOCells = DataRange.Address
End Function

My main problem with this is that it refers back to a cell in the workbook, which I want to try and avoid. I used the ActiveCell for simplicity, if this is the only method I can find I will create a hidden worksheet and refer to a cell on it when I define the range object. I will need to do this becasue I have several routines will need this functionality.

I do like how Excel will yield control to the Function Wizard without me having to store data and unload my user form though.

CMP

 

[Zathras]

If you have Excel 2000 or higher, you can show your form "Modeless" which will allow the user to click and select stuff on the worksheet while the form is still showing.

Unfortunately I only have Excel 97 here so I can't work up an example for you. If you don't have it sorted by tonight, I can work at home where I do have Excel 2K.

I am very impressed by your creative solution using the Function Dialog Wizard that way. I'm adding that to my bag of tricks. That's worth a star to you!

 

[KenWright]

Do you mean like this?:-

Sub SelectRng()
    Set rng = Application.InputBox("Select any Cell/Range", Type:=8)
    MsgBox rng.Address
End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Zathras]

Beautiful! I was always under the impression that stuff would be modal in Excel 97. Works great!

Here is a slightly more robust version that allows the user to cancel without generating an error:

Sub SelectRng()
Dim rng As Range
  On Error GoTo Cancel
  Set rng = Application.InputBox("Select any Cell/Range", Type:=8)
  MsgBox rng.Address
  GoTo ExitSub
Cancel:
  MsgBox "Cancel"
ExitSub:
  On Error GoTo 0
End Sub

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CautionMP]

KenWright and Zathras,

Thank you both for your contributions.

Since I was working with a deadline I went ahead and re-created the functionality of the FormulaWizard. My form now changes size, changes it's apperance, moves around the screen, and yields focus back to the workbook so the user can select the range of cells they need.

The only drawback is that the selected range (a text box on the form) does not update real-time like the Wizard does), otherwise the functionality is identical.

My user group is in the 'I know how to use Excel' crowd. This means that if the functionality I provide does not closely mirror what they already do in Excel they are lost (apparently 'knowing' how to use Excel means you don't have to read the instructions or follow the prompts).

Thanks again,
CMP