| {\rtf1\ansi\ansicpg1252\deff0\deflang1044{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\f0\fs17 The following code demonstrates how to prompt the user to select a range in Excel, using an input box. \par \par Option Explicit \par \par \par 'Purpose : Displays an input box which allows the user to select a range \par 'Inputs : sPrompt The message to be displayed in the dialog box. \par ' sTitle The title for the input box. \par ' oDefaultRange The range which is initially selected. \par 'Outputs : Returns a range object or nothing if the user pressed cancel. \par \par Function RangeUserSelect(sPrompt As String, Optional sTitle As String = "Select a Cell", Optional oDefaultRange As Excel.Range) As Excel.Range \par 'on error resume next \par 'Display the Input Box \par If oDefaultRange Is Nothing Then \par If ActiveCell Is Nothing Then \par Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, , , , , , 8) \par Else \par Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, ActiveCell.Address, , , , , 8) \par End If \par Else \par Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, oDefaultRange.Address, , , , , 8) \par End If \par On Error GoTo 0 \par End Function \par \par Sub Test() \par Dim oRange As Excel.Range \par 'Ask the user which cells to delete, with the cell B2 selected as a default. \par Set oRange = RangeUserSelect("Please select the range to delete", , Range("B2")) \par If oRange Is Nothing = False Then \par 'Delete the cell/s \par MsgBox "Deleting cells " & oRange.Address & " ...", vbInformation \par oRange.Delete \par Set oRange = Nothing \par Else \par 'User pressed cancel \par MsgBox "Cancelled...", vbInformation \par End If \par End Sub \par } |
Selecting a range in Excel using an InputBox |
India web developer web development India | India web development company India ecommerce web developer