VBA To Allow User To Select A Range With An InputBox

I’ve run into a few times where I felt the user experience would be more streamlined if I gave them the option to bring in data or properties from their spreadsheet. You have a couple of options for doing this, as you can either pull this information from:

• the user’s current selection
• a predetermined cell range or object name
• ask the user to type a cell address into an input box

I would argue the last bullet listed above is most likely going to be the most straightforward for your users as they have the most freedom to state any range address they want at that point in time. But is it worth giving the user this much freedom? Is it worth spending countless hours coding to prevent any incorrect input a user might enter into your input box?

This is where the beauty of the built-in VBA InputBox object will save you time and effort. Let’s look at how we can use the InputBox to easily prompt the user to select a cell range so we can store that range location to a variable.

Using The InputBox Object

I won’t go into all the detail of what the InputBox Object can do as you can read all the attributes via Microsoft’s documentation here. However, I will note that the InputBox has some very handy input restrictions that we can use to easily account for all the error handling we need to confirm our users are properly inputting a valid Excel range. This attribute is called the Type.

InputBox “Type” Attribute

InputBox Object Attributes

Below are all the attributes you may modify while using the InputBox object. The main ones we will be using are the Prompt, Title, and Type attributes.

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

VBA Code Examples

Effectively what the following VBA code examples are going to carry out is prompt the user to either enter or select a cell range with their cursor. This InputBox also has the ability to reference different worksheets within the same workbook file. This flexibility really allows your user to have the best experience while providing your VBA macro a variable cell range to work with.

Grab A Cell Range

In this VBA code example, the macro’s goal will be to retrieve a Custom Number Format rule from the user and apply it to the user’s current cell selection. The InputBox will be used to gather a single cell input from the user and store that cell and all it’s properties to a variable. This way, the user does not need to type out the number format rule themselves, they can simple point to a cell that already has the rule applied. This technique is also extremely useful to getting color inputs from your users.

Sub NumberFormatFromCell()
'PURPOSE: Obtain A Number Format Rule From A Cell User's Determines

Dim rng As Range
Dim FormatRuleInput As String

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Number Format Rule From Cell", _
      Prompt:="Select a cell to pull in your number format rule", _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then Exit Sub
  
'Set Variable to first cell in user's input (ensuring only 1 cell)
  Set rng = rng.Cells(1, 1)

'Store Number Format Rule
  FormatRuleInput = rng.NumberFormat

'Apply NumberFormat To User Selection
  If TypeName(Selection) = "Range" Then
    Selection.NumberFormat = FormatRuleInput
  Else
    MsgBox "Please select a range of cells before running this macro!"
  End If

End Sub

Grab A Cell Range With A Default

The below VBA macro code shows you how to display a default cell range value when the InputBox first launches. In this example, the default range will be the users current cell selection.

Sub HighlightCells()
'PURPOSE: Apply A Yellow Fill Color To A Cell Range

Dim rng As Range
Dim DefaultRange As Range
Dim FormatRuleInput As String

'Determine a default range based on user's Selection
  If TypeName(Selection) = "Range" Then
    Set DefaultRange = Selection
  Else
    Set DefaultRange = ActiveCell
  End If

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Highlight Cells Yellow", _
      Prompt:="Select a cell range to highlight yellow", _
      Default:=DefaultRange.Address, _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then Exit Sub
  
'Highlight Cell Range
  rng.Interior.Color = vbYellow

End Sub

Grab A Cell Range Using A Userform

If you want to use the InputBox technique within a userform, I highly recommend hiding your userform before prompting the user with the InputBox. The following code is an example of how you might accomplish this.

Sub NumberFormatFromCell()
'PURPOSE: Obtain A Number Format Rule From A Cell User's Determines

Dim rng As Range
Dim FormatRuleInput As String

'Temporarily Hide Userform
  Me.Hide

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Number Format Rule From Cell", _
      Prompt:="Select a cell to pull in your number format rule", _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then
    Me.Show 'unhide userform
    Exit Sub
  End If
  
'Set Variable to first cell in user's input (ensuring only 1 cell)
  Set rng = rng.Cells(1, 1)

'Store Number Format Rule
  FormatRuleInput = rng.NumberFormat

'Apply NumberFormat To User Selection
  If TypeName(Selection) = "Range" Then
    Selection.NumberFormat = FormatRuleInput
  Else
    MsgBox "Please select a range of cells before running this macro!"
  End If

'Unhide Userform
  Me.Show

End Sub

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Don’t Show Update External Links Dialog While Opening Files With VBA

What This VBA Code Does You are most likely reading this article because you thought turning off ScreenUpdating and DisplayAlerts...

Ask If User Wants To Save File Before Executing VBA Macro

What This VBA Code Does In Excel and Microsoft Word you cannot undo a macro once it has run. Sometimes...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.