VB and VBA Users Source Code: Selecting a range in Excel using an InputBox
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Selecting a range in Excel using an InputBox
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, August 01, 2001
Hits:
1048
Category:
Unspecified
Article:
The following code demonstrates how to prompt the user to select a range in Excel, using an input box. Option Explicit 'Purpose : Displays an input box which allows the user to select a range 'Inputs : sPrompt The message to be displayed in the dialog box. ' sTitle The title for the input box. ' oDefaultRange The range which is initially selected. 'Outputs : Returns a range object or nothing if the user pressed cancel. 'Author : Andrew Baker 'Date : 1/Aug/2001 'Notes : Function RangeUserSelect(sPrompt As String, Optional sTitle As String = "Select a Cell", Optional oDefaultRange As Excel.Range) As Excel.Range On Error Resume Next 'Display the Input Box If oDefaultRange Is Nothing Then If ActiveCell Is Nothing Then Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, , , , , , 8) Else Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, ActiveCell.Address, , , , , 8) End If Else Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, oDefaultRange.Address, , , , , 8) End If On Error GoTo 0 End Function Sub Test() Dim oRange As Excel.Range 'Ask the user which cells to delete, with the cell B2 selected as a default. Set oRange = RangeUserSelect("Please select the range to delete", , Range("B2")) If oRange Is Nothing = False Then 'Delete the cell/s MsgBox "Deleting cells " & oRange.Address & " ...", vbInformation oRange.Delete Set oRange = Nothing Else 'User pressed cancel MsgBox "Cancelled...", vbInformation End If End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
