Excel Pragma » Prompt for Range

January 14, 2005

Prompt for Range

Filed under: Forms, General, Utilities — Fadi @ 5:36 pm

No matter what type of sheets you’re developing, chances are at one point or the other, you will need to prompt the user to select a range to be used in your functions and procedures.

The code below will prompt the user to select a range and return NOTHING if the prompt was cancelled :

Private Function PromptForRange(Prompt As String, Title As String) As Range

    Dim CurRange As Range

    On Error Resume Next
    Set CurRange = Application.InputBox( _
        Prompt:=Prompt, _
        Title:=Title, _
        Default:=ActiveCell.Address, _
        Type:=8) ‘Range selection

‘   Was the Input Box canceled?
    If CurRange Is Nothing Then
        MsgBox “Canceled.”
        Set PromptForRange = Nothing
    Else
        Set PromptForRange = CurRange
    End If

End Function

to call this function from other procedures, remember to check whether the prompt was cancelled by the user :

Dim OutCell, UserRange As Range

‘——————————- GET INPUT RANGE ———————————–
sp; On Error Resume Next
Set UserRange = PromptForRange(”Select the output cell”, “Select the output cell”)

‘   Was the Input Box canceled?
    If UserRange Is Nothing Then
        Exit Sub
    Else
        Set OutCell = UserRange
    End If
‘——————————- GET INPUT RANGE ————————————-

2 Comments

Nice site. Just a heads up, I have a problem when copying and pasting your code into the VBE in Excel 2003, somehow the font you use doesn’t translate well into Excel and the code takes some rework. Anyways,, good luck with the site,

Mark

Comment by Mark — January 17, 2005 @ 6:55 am
true true… didn’t test the copy/paste when I posted. The problem is caused by the quotation mark (VBA comment). I’ll need to revisit the macro that generates the HTML code. Thanks for the note.

Comment by Fadi — January 17, 2005 @ 11:14 am

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.