VBA

Ask User for a Range

VBA provides a number of ways to interact with users. MsgBox lets you send a message to a user, and get a simple response (yes/no or okay/cancel) in return. InputBox lets you ask a user to input some information. And you can design a whole UserForm as a custom dialog.

We will use an InputBox to get a range from our user. There are two kinds of InputBox: regular InputBox that accepts text input from the user, and Application.InputBox, which lets you require a certain type of information from the user. Since we need a range, we’ll use Application.InputBox. The required syntax is:

Function InputBox(Prompt As String, [Title], [Default], _
    [Left], [Top], [HelpFile], [HelpContextID], [Type])

You supply a Prompt to tell the user what you need, and optionally a Title for the InputBox and a Default value. Left and Top position the InputBox but haven’t worked in recent memory. HelpFile and HelpContextID are used if you have help content for the InputBox. Finally, Type describes the type of data you want; for our purposes, type 8 is used for a range.

Here is a simple VBA procedure that uses Application.InputBox. It uses simple prompt, title, and default arguments, plus the 8 for range, and the in between arguments are left blank. When a range is returned, those cells are filled red.

Sub GetUserRange1()
  Dim UserRange As Range
  Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8)
  UserRange.Interior.Color = vbRed
End Sub

When the code runs, here is how it looks. The default range is highlighted with a dark dashed outline.

When another range is selected, its address appears in the InputBox, and the new range is highlighted a dark dashed outline.

When we then click OK, the selected cell is filled red.

If we try to enter anything that isn’t a range, we get a warning.

If we give up and hit cancel, we get a run time error. The statement calling InputBox is highlighted.

The warning is fine, but we don’t want to saddle our poor user with the run time error, so we will make a minor modification to the code. Actually, two modifications. First, we wrap the call to InputBox in On Error Resume Next and On Error GoTo 0. Then, if the user has canceled, UserRange will not have been assigned, so it is Nothing, and if this is the case, we’ll bail out before trying to format a nonexistent range, which would lead to another error.

Sub GetUserRange2()
  Dim UserRange As Range
  On Error Resume Next
  Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8)
  On Error GoTo 0
  If UserRange Is Nothing Then Exit Sub
  UserRange.Interior.Color = vbRed
End Sub

Execution exits the sub above in two places, either Exit Sub if UserRange Is Nothing or End Sub at the end. If you have a bit of OCD like I do, you’d prefer the code to always exit at one place, so I usually use this structure instead:

Sub GetUserRange2A()
  Dim UserRange As Range
  On Error Resume Next
  Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8)
  On Error GoTo 0
  If Not UserRange Is Nothing Then
    UserRange.Interior.Color = vbRed
  End If
End Sub

Let’s punch up the code with a few text variables, and allow for a smart default range (or you can omit the default to leave out blank if that makes more sense).

Sub GetUserRange3()
  Dim Prompt As String
  Prompt = "Select a Range"
  Dim Title As String
  Title = "Select Range"
  Dim Default As String
  Default = "$A$1"
  If TypeName(Selection) = "Range" Then
    Default = Selection.Address(True, True)
  End If
  Dim UserRange As Range
  On Error Resume Next
  Set UserRange = Application.InputBox(Prompt, Title, Default, , , , , 8)
  On Error GoTo 0
  If Not UserRange Is Nothing Then
    UserRange.Interior.Color = vbRed
  End If
End Sub

So now that we know how to get a range from the user, let’s put it to better use than just formatting a range to have a red background.

Select Range and Assign Series Names

In this example, we’ll ask the user for a range containing series names, and we’ll apply the range to the active chart’s series one by one, until we run out of series in the chart or cells in the selected range.

Since we’re working on the active chart, the user must select a chart before running the code. We’ll use this structure to make sure there is an active chart:

Sub DoSomethingToActiveChart()
   If ActiveChart Is Nothing Then
     MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
   Else
     ' do our thing here
   End If
 End Sub

Often I just bail out if there’s no active chart, but it’s nice to let the user know why nothing happened when they clicked a button.

In the procedure below, we check first for an active chart, then we ask for a range containing series names. Then we make sure the range is a single row or column (not strictly necessary, but otherwise it gets complicated), and step through the series of the chart, using each cell in the range as the title of the series.

Sub SelectRangeWithSeriesNames()
   If ActiveChart Is Nothing Then
     MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
   Else
     Dim Prompt As String
     Prompt = "Select a range that contains series names for the active chart."
     Dim Title As String
     Title = "Select Series Names"
     Dim SeriesNameRange As Range
     On Error Resume Next
     Set SeriesNameRange = Application.InputBox(Prompt, Title, , , , , , 8)
     On Error GoTo 0
     If Not SeriesNameRange Is Nothing Then
       If SeriesNameRange.Rows.Count = 1 Or SeriesNameRange.Columns.Count = 1 Then
         With ActiveChart
           Dim iSrs As Long
           For iSrs = 1 To .SeriesCollection.Count
             If iSrs <= SeriesNameRange.Cells.Count Then
               .SeriesCollection(iSrs).Name = _
                   "=" & SeriesNameRange.Cells(iSrs).Address(, , , True)
             End If
           Next
         End With
       Else
         MsgBox "Select a range with one row or one column", vbExclamation, _
             "Must be One Row or Column"
       End If
     End If
   End If
 End Sub

Here is a chart. The highlighted data range shows that the Y values are in C5:E10, and the category labels (X values) in B5:B10, but no series names are highlighted. The series names Series1 etc. in the legend also indicate that no series names have been specified.

Let’s use the labels in C2:E2 for series names. Run the code: the InputBox is waiting for a range to be selected.

Select a range: it is highlighted with a dark dashed outline.

Click OK and the selected range is now highlighted as the series names, and the chart legend shows these labels as series names.

The above procedure can be used to assign series names (legend entries) for a chart that has none, or to replace the existing names (entries) in a chart that already has them.

If there are not enough cells in the selected range, some series do not get new names assigned. If there are more cells than series, the code ignores the excess cells.

Select Range and Assign Categories

In much the same way, we can ask a user for a range containing category labels (X axis values) to assign to a chart.

Sub SelectRangeWithCategoryLabels()
   If ActiveChart Is Nothing Then
     MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
   Else
     Dim Prompt As String
     Prompt = "Select a range that contains category labels for the active chart."
     Dim Title As String
     Title = "Select Category Labels"
     Dim CategoryLabelRange As Range
     On Error Resume Next
     Set CategoryLabelRange = Application.InputBox(Prompt, Title, , , , , , 8)
     On Error GoTo 0
     If Not CategoryLabelRange Is Nothing Then
       If CategoryLabelRange.Rows.Count = 1 Or CategoryLabelRange.Columns.Count = 1 Then
         With ActiveChart
           If CategoryLabelRange.Cells.Count = .SeriesCollection(1).Points.Count Then
             .SeriesCollection(1).XValues = CategoryLabelRange
           Else
             MsgBox "Select a range with the correct number of points.", vbExclamation, _
                 "Wrong Number of Points"
           End If
         End With
       Else
         MsgBox "Select a range with one row or one column", vbExclamation, _
             "Must be One Row or Column"
       End If
     End If
   End If
 End Sub

The above procedure can be used to assign category labels (X axis values) for a chart that has none, or to replace the existing labels in a chart that already has them.

The selected range must have one row and multiple columns or one column and multiple rows. (In some cases, a chart can have category labels that use multiple rows/columns, but that is a lot more complicated than needed for this example.) If this condition is not met, the code tells the user to try again.

Here is a chart. The highlighted data range shows that the Y values are in D3:F8, and the series names in D2:F2, but no categories are highlighted. The category labels 1, 2, 3, etc. along the X axis also indicate that no categories have been specified.

Let’s use the labels in B3:B8 for categories. Run the code: the InputBox is waiting for a range to be selected.

Select a range: it is highlighted with a dark dashed outline.

Click OK and the selected range is now highlighted as the category range, and the horizontal axis displays these labels as categories.

The above procedure can be used to assign categories (X values) for a chart that has none, or to replace the existing categories in a chart that already has them.

If the selected range has the wrong number of cells for the points in a series, the procedure tells the user to select a range with the right number of labels.

Find Range and Assign Series Names

If we construct a chart from an ideally-shaped range, there is a predictable alignment of X values, Y values, and series names in the worksheet. In this chart, the series data is in columns. The Y values for a series are in a vertical range, and the series name is in the cell directly above these Y values. The category labels (X values) are in the column to the left of the Y values of the first series.

In the next chart, the series data is in rows. The Y values for a series are in a horizontal range, and the series name is in the cell directly to the left these Y values. The X values are in the row above the Y values of the first series.

If for some reason the chart was constructed without series names or without category labels, but the missing elements are in the right alignment with respect to the Y values, we can find the cells with the series names or category labels and assign them to the chart.

Find Range and Assign Series Names

The procedure below processes each series in the active chart. First it looks up the SERIES formula, and extracts its arguments into an array. The code finds the address of the Y values, which is in the third argument, and locates the associated range. Then it determines whether the data is by column or by row, identifies the cell with the series name, and uses this cell’s address as the name of the series.

Sub FindAndApplyNamesToSeries()
   If Not ActiveChart Is Nothing Then
     With ActiveChart
       Dim srs As Series
       For Each srs In .SeriesCollection
         ' series formula
         Dim sFmla As String
         sFmla = srs.Formula
         ' just the arguments
         sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
         ' split into an array
         Dim vFmla As Variant
         vFmla = Split(sFmla, ",")
         ' Y values are the 3rd argument
         Dim sYVals As String
         sYVals = vFmla(LBound(vFmla) + 2)
         ' the Y value range
         Dim rYVals As Range
         Set rYVals = Range(sYVals)
         ' by row or column?
         If rYVals.Rows.Count > 1 Then
           ' by column, so use cell above column of Y values
           Dim rName As Range
           Set rName = rYVals.Offset(-1).Resize(1)
         ElseIf rYVals.Columns.Count > 1 Then
           ' by row, so use cell to left of Y values
           Set rName = rYVals.Offset(, -1).Resize(, 1)
         Else
           ' one cell only: who knows?
           Set rName = Nothing
         End If
         If Not rName Is Nothing Then
           srs.Name = "=" & rName.Address(, , , True)
         End If
       Next
     End With
   End If
 End Sub

The data range for the chart below is intact, but somehow, the series names were not associated with it.

Select the chart and run the procedure, and it applies the series names.

If the series in the chart already had names, this procedure overwrites those names with the names it finds in the worksheet.

Find Range and Assign Categories

The procedure below processes the first series in the active chart. First it looks up the SERIES formula, and extracts its arguments into an array. The code finds the address of the Y values, which is in the third argument, and locates the associated range. Then it determines whether the data is by column or by row, identifies the parallel range with the categories (X values), and uses this range as the categories for the first series, and therefore, for the chart.

Sub FindAndApplyCategoriesToChart()
   If Not ActiveChart Is Nothing Then
     With ActiveChart
       Dim srs As Series
       Set srs = .SeriesCollection(1)
       ' series formula
       Dim sFmla As String
       sFmla = srs.Formula
       ' just the arguments
       sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
       ' split into an array
       Dim vFmla As Variant
       vFmla = Split(sFmla, ",")
       ' Y values are the 3rd argument
       Dim sYVals As String
       sYVals = vFmla(LBound(vFmla) + 2)
       ' the Y value range
       Dim rYVals As Range
       Set rYVals = Range(sYVals)
       ' by row or column?
       If rYVals.Rows.Count > 1 Then
         ' by column, so use column to left of Y values
         Dim rXVals As Range
         Set rXVals = rYVals.Offset(, -1)
       ElseIf rYVals.Columns.Count > 1 Then
         ' by row, so use row above Y values
         Set rXVals = rYVals.Offset(-1)
       Else
         ' one cell only: who knows?
         Set rXVals = Nothing
       End If
       If Not rXVals Is Nothing Then
         srs.XValues = rXVals
       End If
     End With
   End If
 End Sub

The data range for the chart below is intact, but somehow, the categories were not attached to it.

Select the chart and run the procedure, and it applies the categories to the chart.

If the series in the chart already had categories, this procedure overwrites those categories with the new labels it finds in the worksheet.

Select Chart Source Data Range

For completeness, here is the routine that lets the user select the chart source data for the active chart.

Sub SelectChartSourceDataRange()
   If ActiveChart Is Nothing Then
     MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
   Else
     Dim Prompt As String
     Prompt = "Select a range that contains source data for the active chart."
     Dim Title As String
     Title = "Select Chart Source Data Range"
     Dim ChartSourceData As Range
     On Error Resume Next
     Set ChartSourceData = Application.InputBox(Prompt, Title, , , , , , 8)
     On Error GoTo 0
     If Not ChartSourceData Is Nothing Then
       ' if a range was selected
       If ChartSourceData.Rows.Count >= ChartSourceData.Columns.Count Then
         Dim DataOrientation As XlRowCol
         DataOrientation = xlColumns
       Else
         DataOrientation = xlRows
       End If
       ActiveChart.SetSourceData ChartSourceData, DataOrientation
     End If
   End If
 End Sub

Jon’s Toolbox

If you follow this blog closely, you may have heard of Jon’s Toolbox, a new Excel add-in that I released recently. I’ve already made a few changes to this utility, thanks to suggestions from readers like you.

Jon’s Toolbox already had a feature that made it easy to select a new source data range for a chart. While finishing up this article, I realized that these features should be included in the software.

The features have only been added to the DEV version of the software on my computer, but in a week or so I will release a new build that includes these new functions. If you already have Jon’s Toolbox, subscribe to my newsletter to hear when the new build is live. If you don’t have Jon’s Toolbox, you can get it at the bottom of my article Jon’s Toolbox – A New Utility from Peltier Tech.